Using pivot on multiple columns of an Oracle row

oracle, oracle11g, pivot, sql

Solution

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.

Problem

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?

Original source