Convert columns to rows in SQL [duplicate]

Here's another one you might have some luck with. I like @ThinkJet's but not sure how much decode costs (if more or less than this below.

SELECT   T1.ID,   T1.fname,   T1.lname,   T2.fname,   T2.lname,   T3.fname,   T3.lname,   T4.fname,   T4.lnameFROM      table T1   LEFT JOIN      table T2   ON         T1.ID = T2.ID      AND T2.count = 2   LEFT JOIN      table T3   ON         T1.ID = T3.ID      AND T3.count = 3   LEFT JOIN      table T4   ON         T1.ID = T4.ID      AND T4.count = 4WHERE   T1.count = 1

Look at this example, same principle as in @Mike M. answer, but with true Oracle realization:

  create table my_table (    id    number,    fname varchar2(255),    lname varchar2(255),    cnt   number  );  insert into my_table(cnt, fname, lname, id) values(1,'abc','def',20);  insert into my_table(cnt, fname, lname, id) values(2,'pqr',''   ,20);        insert into my_table(cnt, fname, lname, id) values(3,'abc','xyz',20);    insert into my_table(cnt, fname, lname, id) values(4,'xyz','xyz',20);  insert into my_table(cnt, fname, lname, id) values(1,'abc','def',21);  insert into my_table(cnt, fname, lname, id) values(1,'pqr','xyz',22);  insert into my_table(cnt, fname, lname, id) values(2,'abc','abc',22);  select     tbl.id,    min(decode(tbl.cnt, 1 , fname, null)) fname_1,    min(decode(tbl.cnt, 1 , lname, null)) lname_1,    min(decode(tbl.cnt, 2 , fname, null)) fname_2,    min(decode(tbl.cnt, 2 , lname, null)) lname_2,    min(decode(tbl.cnt, 3 , fname, null)) fname_3,    min(decode(tbl.cnt, 3 , lname, null)) lname_3,    min(decode(tbl.cnt, 4 , fname, null)) fname_4,    min(decode(tbl.cnt, 4 , lname, null)) lname_4  from     my_table tbl  group by     tbl.id   order by     tbl.id    ;

I know you're after an Oracle 9i solution, but Oracle 11 introduces PIVOT, which allows you to do queries like:

select *  from mb_test pivot ( max(fname ) as fname,         max(lname) as lname   for count in (1,2,3,4)       )order by id;

which gives:

 ID  1_fname  1_lname  2_fname  2_lname  3_fname  3_lname  4_fname  4_lname20  abc      def      pqr      null     abc      xyz      xyz      xyz21  abc      def      null     null     null     null     null     null22  pqr      xyz      abc      abc      null     null     null     null

Not quite what you were after, but extremely useful in many circumstances.... and almost worth the upgrade for PIVOT and UNPIVOT alone

EDIT

Modified to put fname and lname in separate columns