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?

tdsimpso
tdsimpso asked
on
Medium Priority
913 Views
Last Modified: 2012-05-05

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

Author

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.

Ask the Experts

Author

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(+)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.