SQL: How can i build a string from column values of one row?

Question

I have rows like this...

|  NAME  |   RED  |  BLUE  |  GREEN  |
  LeAnn       1        0         1
  Jim         0        0         0
  Timothy     1        1         1

I want to write a select statement that would return the following rows...

| Name    | Note |
  LeAnn     RED, Green
  Jim       
  Timothy   RED, BLUE, GREEN

How can I do this? I tried using case statement but would only get one value in Note instead of multiple values. Thanks!

My Attempt:

SELECT Name, 
       CASE
         WHEN Red = 1 THEN 'Red' + ', '
         WHEN Blue = 1 THEN 'Blue' + ', '
         WHEN Green = 1 THEN 'Green' + ', '
       END as Note
  FROM MyTable

Answer 1

Since the cases are not mutually exclusive, you need three separate cases:

SELECT ('' + 
    CASE WHEN Red = 1 THEN 'Red, ' ELSE '' END
+   CASE WHEN Blue = 1 THEN 'Blue, ' ELSE '' END
+   CASE WHEN Green = 1 THEN 'Green, ' ELSE '' END
)
AS Note
FROM MyTable