CODEWITHSUNDEEP
sqloracle-database
banana_99
banana_99

Reputation: 641

Oracle pivot 2 columns

I have this table:

CA  C   P1  P2
--------------
CA1 C1  01  01
CA2 C2  02  02
CA1 C1  03  01

And I would like to obtain the below output:

CA  C   01_01   01_02   02_01   02_02   03_01   03_03
-----------------------------------------------------
CA1 C1      1       0       0       0       1       0
CA2 C2      0       0       0       1       0       0

This would be a double pivot showing the columns as P2_P1 with values as count of the occurrences in the original table.

Upvotes: 0

Views: 49

Answers (2)

astentx
astentx

Reputation: 6750

You need to use tuples in the for and in clauses of pivot. Like this:

with a(CA, C, P1, P2) as (
  select 'CA1', 'C1', '01', '01' from dual union all
  select 'CA2', 'C2', '02', '02' from dual union all
  select 'CA1', 'C1', '03', '01' from dual
)
select *
from a
pivot(
  count(*) for (p1, p2) in (
    ('01', '01') as "01_01",
    ('01', '02') as "01_02",
    ('02', '01') as "02_01",
    ('02', '02') as "02_02",
    ('03', '01') as "03_01",
    ('03', '02') as "03_02"
  )
)

CA  | C  | 01_01 | 01_02 | 02_01 | 02_02 | 03_01 | 03_02
:-- | :- | ----: | ----: | ----: | ----: | ----: | ----:
CA1 | C1 |     1 |     0 |     0 |     0 |     1 |     0
CA2 | C2 |     0 |     0 |     0 |     1 |     0 |     0

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Just use conditional aggregation:

select ca, c,
       sum(case when p1 = '01' and p2 = '01' then 1 else 0 end) as val_0101,
       sum(case when p1 = '01' and p2 = '02' then 1 else 0 end) as val_0102,
       sum(case when p1 = '02' and p2 = '01' then 1 else 0 end) as val_0201,
       sum(case when p1 = '02' and p2 = '02' then 1 else 0 end) as val_0202,
       sum(case when p1 = '03' and p2 = '01' then 1 else 0 end) as val_0301,
       sum(case when p1 = '03' and p2 = '02' then 1 else 0 end) as val_0302
from t
group by ca, c

Upvotes: 1

Related Questions