CODEWITHSUNDEEP
sqlselectsql-server-2008-r2case-statement
daveomcd
daveomcd

Reputation: 6555

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

Upvotes: 2

Views: 5256

Answers (5)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

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

Upvotes: 2

Tomalak
Tomalak

Reputation: 338158

SELECT
  ''
  + ISNULL(NULLIF(Red, 0), 'Red, ')
  + ISNULL(NULLIF(Blue, 0), 'Blue, ')
  + ISNULL(NULLIF(Green, 0), 'Green, ')
  AS Note
FROM 
  TheTable

This will create a trailing comma that you'd have to trim. For example like this:

SELECT
  REPLACE(
    ''
    + ISNULL(NULLIF(Red, 0), 'Red, ')
    + ISNULL(NULLIF(Blue, 0), 'Blue, ')
    + ISNULL(NULLIF(Green, 0), 'Green, ') 
    + '$',
    ', $',
    ''
  ) AS Note
FROM 
  TheTable

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

What about this?

select name, left(Note, abs(len(Note) - 1)) from (
  select name, '' +
      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 t
) final

Upvotes: 0

Zork
Zork

Reputation: 1119

SELECT Name, CASE WHEN RED = 1 THEN "RED " ELSE "" END + CASE WHEN BLUE = 1 THEN "BLUE " ELSE "" END + CASE WHEN GREEN = 1 THEN "GREEN" ELSE "" END
FROM tableName

There needs to be 3 separate cases, or it will only do one of them.

You would need to update the above to handle the commas properly as well, but im too lazy to put that in

Upvotes: 1

pascalvgemert
pascalvgemert

Reputation: 1247

SELECT Name, 
   CONCAT(
      IF(Red = 1, 'Red', ''),
      IF(Red = 1 AND Blue = 1, ', ', ''),
      IF(Blue = 1, 'Blue', ''),
      IF(Red = 1 AND Green = 1 AND `Blue` = 0, ', ', ''),
      IF(Blue = 1 AND Green = 1, ', ', ''),
      IF(Green = 1, 'Green', '')
   ) AS Note
From Table

I think this should work! Let me know if it doesn't ;)

Upvotes: 0

Related Questions