Append a column values from multiple rows into a string

Question

Is this valid SQL to append the values of a column for several records into a single string result?

DECLARE @Result NVARCHAR(MAX)
SET @Result = ''

SELECT @Result = @Result + Answer + ';'
FROM Answers A
WHERE A.Quiz_ID = 1 AND A.Question_Id = 1
ORDER BY Answer

Select @Result [List Of Answers]

The result should look like this:

Can You Repeat The Question;No;Yes;

This is only an example. My question is is this valid SQL? The reason I ask is that this technique seems to break down in my real code with more joins and an ORDER BY clause. In my "real" code I only get the value from one row.

have you tried running it?

Jean-Bernard Pellerin
– Jean-Bernard Pellerin

2013-06-21 05:14:43 +00:00
Commented Jun 21, 2013 at 5:14 — Jean-Bernard Pellerin
– Jean-Bernard Pellerin, Commented Jun 21, 2013 at 5:14

peterm · Accepted Answer · 2013-06-21 05:50:36Z

To achieve your goal you need to use STUFF() and GROUP BY

SELECT q.Question + ';' +
       STUFF((SELECT ';' + Answer
                FROM Answers
               WHERE Question_Id = q.Question_Id
               ORDER BY 1
                 FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)'),1,1,'') result
  FROM Questions q LEFT JOIN Answers a
    ON q.Question_Id = a.Question_Id
 GROUP BY q.Question_Id, q.Question

Sample output:

|                                      RESULT |
-----------------------------------------------
|    Can You Repeat The Question;Maybe;No;Yes |
| Can You Really Repeat The Question;Sure;Yes |

Here is SQLFiddle demo

If there is a possibility of having questions without answers being assigned yet and you want to properly show them you may use ISNULL() or COALESCE() (e.g. to inject a default value)

SELECT q.Question + ';' +
       ISNULL(STUFF((SELECT ';' + Answer
                FROM Answers
               WHERE Question_Id = q.Question_Id
               ORDER BY 1
                 FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)'),1,1,''), 
       '[Sorry, no answers yet]') result
  FROM Questions q LEFT JOIN Answers a
    ON q.Question_Id = a.Question_Id
  GROUP BY q.Question_Id, q.Question

Sample output:

|                                                 RESULT |
----------------------------------------------------------
|               Can You Repeat The Question;Maybe;No;Yes |
|            Can You Really Repeat The Question;Sure;Yes |
| Can a question have no answers;[Sorry, no answers yet] |

Here is SQLFiddle demo

Collectives™ on Stack Overflow

Append a column values from multiple rows into a string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related