syntaxhelp.com Your online cheat sheet!

Creating a comma separated (delimited) with TSQL

SET NOCOUNT ON
--Declaring temporary table
DECLARE @QuestionAnswer TABLE (QuestionId INT, PotentialAnswerId INT)

--Insert data
INSERT INTO @QuestionAnswer VALUES (101, 50)
INSERT INTO @QuestionAnswer VALUES (101, 51)
INSERT INTO @QuestionAnswer VALUES (101, 59)
INSERT INTO @QuestionAnswer VALUES (102, 52)
INSERT INTO @QuestionAnswer VALUES (102, 55)
INSERT INTO @QuestionAnswer VALUES (103, 90)
INSERT INTO @QuestionAnswer VALUES (103, 91)

--Sample data
SELECT * FROM @QuestionAnswer

/*
OUTPUT:
QuestionId  PotentialAnswerId
----------- -----------------
101         50
101         51
101         59
102         52
102         55
103         90
103         91
*/

--Query to do string aggrigation
SELECT QuestionId,
    REPLACE((SELECT 
        PotentialAnswerId AS 'data()'
     FROM @QuestionAnswer c2 
     WHERE c2.QuestionId = c1.QuestionId
     FOR XML PATH('')), ' ', ',') AS PotentialAnswers
FROM @QuestionAnswer c1
GROUP BY QuestionId

/*
OUTPUT
QuestionId  PotentialAnswers
----------- -----------------
101         50,51,59
102         52,55
103         90,91
*/