I have a table like this:

In this table, id and source make up a unique key. source can be one of 'a', 'b' or 'c'.

I want to transform it into this:

I don't mind if the 0s are nulls instead, if that would make it easier.

The target table should be the result of the following steps:

for every row in the table:
    if source = 'a', add or update a row with id and score_a = score
    if source = 'b', add or update a row with id and score_b = score
    if source = 'c', add or update a row with id and score_c = score

I tried the following statement:

select id, score as score_a, null as score_b, null as score_c from tbl where source = 'a'
union all
select id, null as score_a, score as score_b, null as score_c from tbl where source = 'b'
union all
select id, null as score_a, null as score_b, score as score_c from tbl where source = 'c'

But it gave me this result instead:

How can I get a table like the one I want (with distinct id)?

CodePudding user response：

You just need to use CASE to implement your logic and then flatten the result with some aggregation (I used MAX())

SELECT id,
       MAX(CASE WHEN source='a' THEN score END) as score_a,
       MAX(CASE WHEN source='b' THEN score END) as score_b,
       MAX(CASE WHEN source='c' THEN score END) as score_c
FROM Tbl
GROUP BY id

DEMO

You indicated that null was OK, but if you have to have zeroes instead add COALESCE()