We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# How can I flip the results?

on
Medium Priority
913 Views

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?
Comment
Watch Question

## View Solution Only

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
Commented:
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>

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:

Excellent.
Thank you.

Commented:
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(+)
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile