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?
tdsimpsoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tdsimpsoAuthor Commented:

Oh, I am using Oracle 9.2.
I have seen this before some time ago, but I forgot how it was done.

Thanks,
Troy
0
RCorfmanCommented:
If you are always looking for c,r,u,d as the values, then this can be done...
I'm going to assume that the commas are different column values.

I'm sure there are more ways to do this, but here's one for you.
(if you didn't want the word null, but a null column, change 'null' below to null 4 times).

select id,
  (select decode(count(*),0,'null','c') from tab1 inner
    where inner.id=outer.id and inner.val='c') c,
  (select decode(count(*),0,'null','r') from tab1 inner
    where inner.id=outer.id and inner.val='r') r,
  (select decode(count(*),0,'null','u') from tab1 inner
    where inner.id=outer.id and inner.val='u') u,
  (select decode(count(*),0,'null','d') from tab1 inner
    where inner.id=outer.id and inner.val='d') d
from tab1 outer
group by id
order by id;

SQL> select * from tab1;

        ID V
---------- -
       101 c
       101 r
       101 u
       101 d
       102 r
       102 u

6 rows selected.

SQL> select id,
  2    (select decode(count(*),0,'null','c') from tab1 inner
  3      where inner.id=outer.id and inner.val='c') c,
  4    (select decode(count(*),0,'null','r') from tab1 inner
  5      where inner.id=outer.id and inner.val='r') r,
  6    (select decode(count(*),0,'null','u') from tab1 inner
  7      where inner.id=outer.id and inner.val='u') u,
  8    (select decode(count(*),0,'null','d') from tab1 inner
  9      where inner.id=outer.id and inner.val='d') d
 10  from tab1 outer
 11  group by id
 12  order by id;

        ID C    R    U    D
---------- ---- ---- ---- ----
       101 c    r    u    d
       102 null r    u    null

SQL>

Or, if you really wanted a comma seperated list
This would work.
select id,
   (select decode(count(*),0,'null','c') from tab1 inner
     where inner.id=outer.id and inner.val='c')|| ',' ||
   (select decode(count(*),0,'null','r') from tab1 inner
     where inner.id=outer.id and inner.val='r')|| ',' ||
   (select decode(count(*),0,'null','u') from tab1 inner
     where inner.id=outer.id and inner.val='u')|| ',' ||
   (select decode(count(*),0,'null','d') from tab1 inner
     where inner.id=outer.id and inner.val='d') as crud_list
 from tab1 outer
 group by id
 order by id



SQL> select id,
  2     (select decode(count(*),0,'null','c') from tab1 inner
  3       where inner.id=outer.id and inner.val='c')|| ',' ||
  4     (select decode(count(*),0,'null','r') from tab1 inner
  5       where inner.id=outer.id and inner.val='r')|| ',' ||
  6     (select decode(count(*),0,'null','u') from tab1 inner
  7       where inner.id=outer.id and inner.val='u')|| ',' ||
  8     (select decode(count(*),0,'null','d') from tab1 inner
  9       where inner.id=outer.id and inner.val='d') as crud_list
 10   from tab1 outer
 11   group by id
 12   order by id;

        ID CRUD_LIST
---------- ------------------------------
       101 c,r,u,d
       102 null,r,u,null

SQL>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tdsimpsoAuthor Commented:

Excellent.
Thank you.
0
radja7Commented:
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(+)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.