Question:
This question already has an answer here:
- Oracle SQL pivot query 2 answers
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
EmoticonEmoticon