How Can you Create a comma separated value for all the same Id in a column on the fly

–Test Data
DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)
SELECT * FROM @Table1
–Query

SELECT ID
,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
FROM @Table1
WHERE ID = t.ID
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

WITH NVARCHAR

--Test Data
CREATE TABLE #Table1 (Name NVARCHAR(MAX), Query NVARCHAR(MAX),Id Nvarchar(Max))
INSERT INTO #Table1 VALUES ('Task','ABC','11'),('Task1','ABC','11'),('Task3','ABC','11'),('Task4','ABC','11')
SELECT * FROM #Table1

--Query
DECLARE @CPFFileName NVARCHAR(MAX)
SET @CPFFileName = (SELECT STUFF((SELECT ', ' + Name [text()]
FROM #Table1
WHERE Query = t.Query
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') FileName
FROM #Table1 t
GROUP BY t.Query)
UPDATE #Table1 SET Name= @CPFFileName
SELECT * FROM #Table1
DROP TABLE #Table1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s