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?

Really great question, thanks!

interestedparty333
– interestedparty333

2016-03-04 00:43:59 +00:00
Commented Mar 4, 2016 at 0:43 — interestedparty333
– interestedparty333, Commented Mar 4, 2016 at 0:43

Alex Poole · Accepted Answer · 2024-10-16 10:25:20Z

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

Amit S · Accepted Answer · 2019-06-13 13:43:19Z

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.

SherlockSpreadsheets · Accepted Answer · 2020-02-03 21:36:01Z

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

Collectives™ on Stack Overflow

Using pivot on multiple columns of an Oracle row

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related