• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 876
  • Last Modified:

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?
0
tdsimpso
Asked:
tdsimpso
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now