How to separate two items of same column into two columns?-CodePudding

ColorBreakdown:

id	color	amount
0	red	25
0	blue	13
1	red	16
1	blue	17

Color:

name
red
blue

How do I separate the two into table below without altering tables (by selecting, joining, filtering, etc.)?

id	red	blue
0	25	13
0	16	17

I tried:

SELECT id, c.amount
FROM Color
INNER JOIN ColorBreakdown AS c ON (Color.name = ColorBreakdown.color);

It only gives red color for each id:

id	c.amount
0	25
0	16

Then I tried:

SELECT id, 
       SUM(color = 'red') AS red, 
       SUM(color = 'blue') AS blue
FROM ColorBreakdown
GROUP BY id;

But it gave:

id	red	blue
0	1	1
0	1	1

CodePudding user response：

You need to use group by and case to achieve this

a sample query is something like :

SELECT id, 
       sum(case when color = 'red' then amount else 0 end) AS red,
       sum(case when color = 'blue' then amount else 0 end) AS blue
FROM ColorBreakdown
group by id;