Using pivot on multiple columns of an Oracle row

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.

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.

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