sql - DB2 Pivot equivalent

Question

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

question from:https://stackoverflow.com/questions/66053075/db2-pivot-equivalent

Answer 1

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.