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 type, weight, height 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 list them instead of using select * (which should generally be avoided anyway, except for ad hoc or inner queries):

select a_ct, b_ct, c_ct, a_wt, b_wt, c_wt, a_ht, b_ht, c_ht
from (
  select type, weight, height 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

---

If those are the only columns in your table then you don't need either subquery:

select a_ct, b_ct, c_ct, a_wt, b_wt, c_wt, a_ht, b_ht, c_ht
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)
);

... but if there are other columns you'll get more rows than you expect.

fiddle