tdsimpso
asked on
How can I flip the results?
I have a table defined with records like the following:
101,c
101,r
101,u
101,d
102,r
102,u
I need a query that flip the results to look like this:
101,c,r,u,d
102,null,r,u,null
Any ideas how to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent.
Thank you.
If set is defined and = {c,r,u,d}
then this query can help:
SELECT t.first_col
,tc.second_col AS c
,tr.second_col AS r
,tu.second_col AS u
,td.second_col AS d
FROM (SELECT first_col FROM table GROUP BY first_col) t
,(SELECT first_col,second_col FROM table WHERE second_col='c') tc
,(SELECT first_col,second_col FROM table WHERE second_col='r') tr
,(SELECT first_col,second_col FROM table WHERE second_col='u') tu
,(SELECT first_col,second_col FROM table WHERE second_col='d') td
WHERE t.first_col=tc.first_col(+ )
AND t.first_col=tu.first_col(+ )
AND t.first_col=tr.first_col(+ )
AND t.first_col=td.first_col(+ )
then this query can help:
SELECT t.first_col
,tc.second_col AS c
,tr.second_col AS r
,tu.second_col AS u
,td.second_col AS d
FROM (SELECT first_col FROM table GROUP BY first_col) t
,(SELECT first_col,second_col FROM table WHERE second_col='c') tc
,(SELECT first_col,second_col FROM table WHERE second_col='r') tr
,(SELECT first_col,second_col FROM table WHERE second_col='u') tu
,(SELECT first_col,second_col FROM table WHERE second_col='d') td
WHERE t.first_col=tc.first_col(+
AND t.first_col=tu.first_col(+
AND t.first_col=tr.first_col(+
AND t.first_col=td.first_col(+
ASKER
Oh, I am using Oracle 9.2.
I have seen this before some time ago, but I forgot how it was done.
Thanks,
Troy