CODEWITHSUNDEEP
sqldatabasedb2
brucezepplin
brucezepplin

Reputation: 9752

sql db2 transform to wide format

I have a table like this:

id type
-- ----
1  A
1  B
2  A
2  C

And I would like to transform this to wide format i.e.

id typeA typeB typeC
-- ----- ----- -----
1  yes   yes   NULL
2  yes   NULL  yes

I have tried to do this by:

select distinct id,
case when type = 'A' then yes else NULL end as typeA
case when type = 'B' then yes else NULL end as typeB
case when type = 'C' then yes else NULL end as typeC
from test

but as you can guess I have ended up with:

id typeA typeB typeC
-- ----- ----- -----
1  yes   NULL   NULL
1  NULL  yes    NULL
1  NULL  NULL   NULL
2  yes   NULL   NULL
2  NULL  NULL   NULL
2  NULL  NULL   yes

i.e. I have ended up with a sparse table, but I would like strictly one row per id.

any ideas?

Upvotes: 1

Views: 193

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Use Min/Max aggregate and Group By to fix it.

SELECT id, 
MIN(CASE WHEN type = 'A' THEN 'yes' ELSE NULL END) AS typeA,
MIN(CASE WHEN type = 'B' THEN 'yes' ELSE NULL END) AS typeB,
MIN(CASE WHEN type = 'C' THEN 'yes' ELSE NULL END) AS typeC 
FROM test 
GROYP BY id

Upvotes: 2

Related Questions