Tutorial :Convert columns to rows in SQL [duplicate]



Question:

This question already has an answer here:

I need to write a query which takes rows and converts it into columns - here's my table:

Count    fname   lname   id  -----------------------------  1        abc     def    20  2        pqr            20        3        abc     xyz    20    4        xyz     xyz    20  1        abc     def    21  1        pqr     xyz    22  2        abc     abc    22  

This is the output I'm trying to produce:

id  fname  lname  fname  lname  fname  lname  fname  lname  -------------------------------------------------------------  20  abc    def    pqr    NULL   abc    xyz    xyz    xyz  21  abc    def    NULL   NULL   NULL   NULL   NULL   NULL     22  abc    abc    NULL   NULL   NULL   NULL   NULL   NULL  

The max value of count for each id is 4. I'm using Oracle 9i.


Solution:1

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.lname  FROM        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 = 4  WHERE     T1.count = 1  


Solution:2

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      ;  


Solution:3

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_lname  20  abc      def      pqr      null     abc      xyz      xyz      xyz  21  abc      def      null     null     null     null     null     null  22  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


Solution:4

Is this what you're looking for?

http://bytes.com/topic/sql-server/answers/531936-convert-rows-into-columns


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »