sqlselectsql-server-2008-r2case-statement

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


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

Solution

  • 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