Transformation of parent-child-hierarchies into a multi-column representation

it's not rocket science but here is a query to transform a balanced parent-child-hierarchy into a multi-column hierarchy for a dimension table:

-- subquery factoring for the relevant part of the source table
with
groups as
(select groupid 
      , groupname
      , parentid
   from ext_groups)
-- definition of the levels of the hierarchy
select ig0.groupid groupid_level0
     , ig0.groupname groupname_level0
     , ig1.groupid groupid_level1
     , ig1.groupname groupname_level1
     , ig2.groupid groupid_level2
     , ig2.groupname groupname_level2
     , ig3.groupid groupid_level3
     , ig3.groupname groupname_level3
     , ig4.groupid groupid_level4
     , ig4.groupname groupname_level4
  from 
       -- root-level with the restriction to nodes without a parent
       -- (in this case: partenid = 0)
       (select *
          from groups
         where parentid = 0
        ) ig0
       -- level 1
     , (select *
          from groups
        ) ig1
       -- level 2
     , (select *
          from groups
        ) ig2
       -- level 3
     , (select *
          from groups
        ) ig3
       -- level 4 (= branch-level)
     , (select *
          from groups
        ) ig4
 where ig1.parentid = ig0.groupid
   and ig2.parentid = ig1.groupid
   and ig3.parentid = ig2.groupid
   and ig4.parentid = ig3.groupid
 order by ig0.groupid
        , ig1.groupid
        , ig2.groupid
        , ig3.groupid
        , ig4.groupid;

The first subquery ig0 gets all the nodes from the root level, then these rows are joined to ig1 via ig1.parentid = ig0.groupid and every following level is connected in the same way. Of course you have to know the depth of the hierarchy to choose the fitting number of subqueries.

The approach should also work for unbalanced (=ragged) hierarchies, but then you have to use outer joins (and maybe to replace NULL-values by a known parent-entry).

Somewhat related is the information that Oracle 11 Release 2 has introduced recursive subquery factoring, so you can avoid the ''connect by'' syntax if you like. Rob van Wijk explains some pros and cons of both options in his Blog.