CODEWITHSUNDEEP
sqloracle-databaseunpivot
misurex
misurex

Reputation: 35

Unpivot (or alternative) all columns into rows

I have the following data:

create table test(c1 number,
                  c2 number,
                  c3 number,
                  c4 number,
                  c5 number);

insert into test (c1,c2,c3,c4,c5) values(2000,1844054,50.03,922030,25.01);
insert into test (c1,c2,c3,c4,c5) values(2001,1850861,49.86,937391,25.25);
insert into test (c1,c2,c3,c4,c5) values(2002,1841519,50.32,907995,24.81);
insert into test (c1,c2,c3,c4,c5) values(2003,1804163,49.84,902838,24.94);

I need to translate the data from:

c1   | c2      | c3    | c4     | c5
2000 | 1844054 | 50.03 | 922030 | 25.01
2001 | 1850861 | 49.86 | 937391 | 25.25
2002 | 1841519 | 50.32 | 907995 | 24.81
2003 | 1804163 | 49.84 | 902838 | 24.94

to:

c1 | 2000    | 2001    | 2002    | 2003
c2 | 1844054 | 1850861 | 1841519 | 1804163
c3 | 50.03   | 49.86   | 50.32   | 49.84
c4 | 922030  | 937391  | 907995  | 902838
c5 | 25.01   | 25.25   | 24.81   | 24.94

I didn't succeed with a regular PIVOT, so I ask you for help, thank you.

Upvotes: 1

Views: 624

Answers (3)

Stew Ashton
Stew Ashton

Reputation: 1529

You are transposing, not just pivoting. You need to UNPIVOT, then PIVOT.

select * from test
unpivot(c for col in(c2,c3,c4,c5))
pivot(max(c) for c1 in(2000,2001,2002,2003))
order by col;

CO       2000       2001       2002       2003
-- ---------- ---------- ---------- ----------
C2    1844054    1850861    1841519    1804163
C3      50.03      49.86      50.32      49.84
C4     922030     937391     907995     902838
C5      25.01      25.25      24.81      24.94

Upvotes: 3

123 234
123 234

Reputation: 1

 SELECT * FROM test
 unPIVOT(
    quantity -- unpivot_clause
    FOR product_code --  unpivot_for_clause
    IN ( c2,c3,c4,c5)) 

pivot(max(quantity) for 
c1 in(2000,2001,2002,2003))
order by product_code;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is a pain. You can unpivot and the re-aggregate. Here is one method:

select which,
       max(case when year = 2000 then c end) as val_2000,
       max(case when year = 2001 then c end) as val_2001,
       max(case when year = 2002 then c end) as val_2002,
       max(case when year = 2003 then c end) as val_2003
from ((select c1 as year, 'c2' as which, c2 as c from test) union all
      (select c1 as year, 'c3' as which, c3 as c from test) union all
      (select c1 as year, 'c4' as which, c4 as c from test) union all
      (select c1 as year, 'c5' as which, c5 as c from test) 
     ) x
group by which
order by which;

Here is a db<>fiddle.

Upvotes: 1

Related Questions