[SQL] Using pivot on multiple columns of an Oracle row

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?

Continue reading...