I have a table like below:
NAME1 | NAME2 | NUM
------------------------
AAA | D1 | 0
AAA | D2 | 9
BBB | D1 | 3
AAA | D4 | 2
CCC | D2 | 10
I wish to regroup the table to look like this:
NAME1 |D1| D2| D3| D4
------------------------
AAA | 0| 9 | -1| 2
BBB | 3| -1| -1|-1
CCC |-1| 10| -1|-1
If a NAME1-Dx combination is missing (for x=1,...,4) in the first table, then I want to write the value -1 in the respective Dx field of the second table.
CodePudding user response:
Use a pivot query, with the help of COALESCE()
to replace missing values with -1.
SELECT
NAME1,
COALESCE(MAX(CASE WHEN NAME2 = 'D1' THEN NUM END), -1) AS D1,
COALESCE(MAX(CASE WHEN NAME2 = 'D2' THEN NUM END), -1) AS D2,
COALESCE(MAX(CASE WHEN NAME2 = 'D3' THEN NUM END), -1) AS D3,
COALESCE(MAX(CASE WHEN NAME2 = 'D4' THEN NUM END), -1) AS D4
FROM yourTable
GROUP BY NAME1;