Joining rows and columns in SQL

Current Result:

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.

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.