Current Result:
x
ID | Item | Red | Green | Blue | Yellow
03 balloon 1 0 0 0
03 balloon 0 1 0 0
03 balloon 1 0 0 1
24 ribbon 0 0 1 0
24 ribbon 0 1 0 0
24 ribbon 0 0 0 1
24 ribbon 1 0 0 0
Desired Result:
ID | Item | Red | Green | Blue | Yellow
03 balloon 1 1 0 1
24 ribbon 1 1 1 1
I don’t want them to be repeated like it does in my current result.
This is my query:
SELECT DISTINCT tb1.ID
, tb1.Item
, (CASE WHEN tb2.color = 'Red' THEN 1 END) AS 'Red'
, (CASE WHEN tb2.color = 'Green' THEN 1 END) AS 'Green'
, (CASE WHEN tb2.color = 'Blue' THEN 1 END) AS 'Blue'
, (CASE WHEN tb2.color = 'Yellow' THEN 1 END) AS 'Yellow'
FROM #table1 tb1
LEFT JOIN #table2 tb2 ON tb1.ID = tb2.ID
I have tried without distinct too and I don’t know how to alter to get to make it look like the desired result.
Advertisement
Answer
Use aggregation:
SELECT tb1.ID, tb1.Item
MAX(CASE WHEN tb2.color = 'Red' THEN 1 ELSE 0 END) AS Red,
MAX(CASE WHEN tb2.color = 'Green' THEN 1 ELSE 0 END) AS Green,
MAX(CASE WHEN tb2.color = 'Blue' THEN 1 ELSE 0 END) AS Blue,
MAX(CASE WHEN tb2.color = 'Yellow' THEN 1 ELSE 0 END) AS Yellow
FROM #table1 tb1 LEFT JOIN
#table2 tb2
ON tb1.ID = tb2.ID
GROUP BY tbl1.ID;
Note that your current result should be returning NULL
rather than 0
. I added ELSE 0
, so you explicitly get 0
.
Also, don’t use single quotes for column aliases. Only use single quotes for string and date constants.