Using pivot on multiple columns of an Oracle row

Question

I have the following sample data in an Oracle table (tab1) and I am trying to convert rows to columns. I know how to use Oracle pivot on one column. But is it possible to apply it to multiple columns?

Sample data:

Type  weight  height   A     50      10   A     60      12   B     40      8   C     30      15   

My intended output:

A-count B-count C-count A-weight B-weight C-weight A-height B-height C-height   2       1       1       110      40       30       22       8        15   

What I can do:

with T AS  (select type, weight from tab1 ) select * from T PIVOT ( count(type) for type in (A, B, C, D,E,F) )   

The above query gives me the below result

A B C   2 1 1   

I can replace count(*) with sum(weight) or sum(height) to pivot height or weight. What I am looking to do, but I can't do, is pivot on all three (count, weight and height) in one query.

Can it be done using pivot?

Answer 1

As the documentation shows, you can have multiple aggregate function clauses. So you can do this:

select * from (   select * from tab1 ) pivot (   count(type) as ct, sum(weight) as wt, sum(height) as ht   for type in ('A' as A, 'B' as B, 'C' as C) );  A_CT A_WT A_HT B_CT B_WT B_HT C_CT C_WT C_HT ---- ---- ---- ---- ---- ---- ---- ---- ----    2  110   22    1   40    8    1   30   15  

If you want the columns in the order you showed then add another level of subquery:

select a_ct, b_ct, c_ct, a_wt, b_wt, c_wt, a_ht, b_ht, c_ht from (   select * from (     select * from tab1   )   pivot (     count(type) as ct, sum(weight) as wt, sum(height) as ht     for type in ('A' as A, 'B' as B, 'C' as C)   ) );  A_CT B_CT C_CT A_WT B_WT C_WT A_HT B_HT C_HT ---- ---- ---- ---- ---- ---- ---- ---- ----    2    1    1  110   40   30   22    8   15  

SQL Fiddle.

Answer 2

The second approach to name the columns is even better and solves more problems. I had a requirement where I wanted to sum up the data returned from PIVOT so having column names I could simply add 2 and get the required result in third one -

select a_ct, b_ct, c_ct, a_wt, b_wt, c_wt, a_ht, b_ht, c_ht, a_wt + b_wt + c_wt tot_wt from (   select * from (     select * from tab1   )   pivot (     count(type) as ct, sum(weight) as wt, sum(height) as ht     for type in ('A' as A, 'B' as B, 'C' as C)   ) );  A_CT B_CT C_CT A_WT B_WT C_WT A_HT B_HT C_HT TOT_WT ---- ---- ---- ---- ---- ---- ---- ---- ---- ------    2    1    1  110   40   30   22    8   15 180 

Just beware that aggregate functions (like sum) won't behave as expected if one of the PIVOT column used returns null, in that case I have used CASE statement to get around it.

Hope it helps someone.