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.

Answer 3

The answer from Alex Poole is awesome and helped my with my Oracle query. This made me curious and here I will quickly point out syntax comparison for Oracle for Microsoft PIVOT multiple columns. This is one area where I would actually award Oracle the SQL Simpler Syntax Award (it's a made up award created by me).

Microsoft SQL is not nearly as simple and flexible when needing to pivot multiple columns.

• You have to concatenate the original grouping field to make it distinct
• The column name aliasing is much more manual in MSFT SQL; The code below doesn't have aliasing
• You have to do mutiple PIVOT calls
• You must alias the PIVOT function to avoid syntax error
• Even with all these things considered it still doesn't group as desired
• Bottom line, Oracle wins the the SQL Simpler Syntax Award

Given the same data set:

DECLARE @tblSampleData AS table (Type nvarchar(10), Weight numeric(5,2), Height numeric(5,2))

INSERT INTO @tblSampleData (Type, Weight, Height)
VALUES
('A',     50,      10) 
,('A',     60,      12) 
,('B',     40,      8 )
,('C',     30,      15)

Microsoft SQL:

select * from 
(
    select 
        *
        ,concat(Type,'1') as "Type1" 
        ,concat(Type,'2') as "Type2"
    from @tblSampleData
) AS src
pivot (
    count(Type) --as ct, sum(weight) as wt, sum(height) as ht
    for Type in ([A], [B], [C])
) AS pvt1
pivot (
    sum(weight) --as ct, sum(weight) as wt, sum(height) as ht
    for Type1 in ([A1], [B1], [C1])
) AS pvt1
pivot (
    sum(height) --as ct, sum(weight) as wt, sum(height) as ht
    for Type2 in ([A2], [B2], [C2])
) AS pvt1