CODEWITHSUNDEEP
sqldb2ibm-midrange
DATAMART
DATAMART

Reputation: 29

DB2 Pivot equivalent

Hoping someone can help, I am working with a db2 database and I am trying to pivot some data to turn it from rows to columns. My data table looks like this

Key1 Key2 Date Type Value
One Key1 01/01/2020 ABC TEST1
One Key1 01/01/2020 DEF TEST2
One Key1 01/01/2020 JKL TEST3
One Key2 01/01/2020 GHI TEST3
One Key2 02/01/2020 ABC TEST3
Two Key1 01/01/2020 ABC TEST4
Two Key1 01/01/2020 DEF TEST5
Tow Key2 01/01/2020 GHI TEST6

I would like my results table to look like this

Key1 Key2 Date ABC DEF GHI JKL
One Key1 01/01/2020 TEST1 TEST2 TEST3
One Key2 01/01/2020 TEST 3
One Key2 02/01/2020 TEST3
Two Key1 01/01/2020 TEST4 TEST5
Two Key2 01/01/2020 TEST6

I know there is the pivot statement in SQL and i have tried this solution "https://stackoverflow.com/questions/42492501/db2-pivot-rows-to-columns" but it rolls up everything in Key1 and does not give me the table i would like, so using that solution i only see one row key key1 which is not what i want. Can anyone help please?

Upvotes: 1

Views: 624

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use conditional aggregation:

select key1, key2, date,
       max(case when type = 'ABC' then value end) as abc,
       max(case when type = 'DEF' then value end) as def,
       max(case when type = 'GHI' then value end) as ghi,
       max(case when type = 'JFK' then value end) as jfk
from t
group by key1, key2, date;

Here is a db<>fiddle.

Upvotes: 3

Related Questions