SQL Server - Update a column with other column values as CSV

Question

Script for table and sample data

CREATE TABLE #TEMPTABLE1(ID INT, COL1 NVARCHAR(10))
CREATE TABLE #TEMPTABLE2(ID INT, COL2 NVARCHAR(10))

INSERT INTO #TEMPTABLE1 (ID) VALUES(1)
INSERT INTO #TEMPTABLE1 (ID) VALUES(2)
INSERT INTO #TEMPTABLE1 (ID) VALUES(3)

INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(1,'A')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(1,'B')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(1,'C')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(2,'X')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(2,'Y')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(3,'Z')

I need to update the #TEMPTABLE1 so that

ID COL1
---------
1  A,B,C 
2  X,Y
3  Z

possible duplicate of [How to return multiple values in one column (T-SQL)?](http://stackoverflow.com/questions/122942/how-to-return-multiple-values-in-one-column-t-sql) — Conrad Frix, Jul 28 '11 at 21:37
Joel, I'm not storing the CSV data in the column. This is a part of reporting and I came across this problem and posted here. — Ashish Gupta, Jul 28 '11 at 21:47
If you're not storing the CSV data in a column, then why do you need an UPDATE statement? — Aaron Bertrand, Jul 29 '11 at 03:25

score 7 · Accepted Answer · answered Jul 28 '11 at 21:45

Sounded like OP wanted an update statement.

UPDATE [x]
SET
    [COL1] = STUFF
            (
                (
                    SELECT
                        N',' + [COL2]
                    FROM
                        [#TEMPTABLE2] AS [y]
                    WHERE
                        [y].[ID] = [x].[ID]
                    FOR XML PATH(''), TYPE
                ).value(N'.', N'nvarchar(10)'), 
                1, 1, N''
            )
OUTPUT INSERTED.*
FROM
    [#TEMPTABLE1] AS [x]

score 3 · Answer 2 · answered Jul 28 '11 at 21:37

It seems the key to your question is concatenating the values in #temptable2 based on the id. Jeff Moden has an excellent article on this at Concatenation Functions and Some Tuning Myths

While the entire article is well worth reading, the key thingis that you can easily use Stuff and For XML Path with a subquery to concatenate based on the ID column. Then you can readily use that to update your #temptable1.

Edit to add example

The concatenation would look roughly like:

SELECT t1.ID,        
STUFF((SELECT ','+t2.value
   FROM dbo.TestData t2 
   WHERE t1.ID = t2.SomeID FOR XML PATH('')),1,1,'')   
FROM dbo.TestData t1  
GROUP BY t1.ID

Need to be aware of character entitization when using the FOR XML method. Add INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(3,N'<') and see if the "<" symbol is properly represented. Change to ().value('.', 'nvarchar(10)') to avoid the issue. — etliens, Jul 28 '11 at 21:49

score 1 · Answer 3 · answered Jul 28 '11 at 21:36

First create a function:

CREATE FUNCTION CommaValues(@ID as INT)
returns varchar(500)
as
begin
DECLARE @DelimList as varchar(500)

select @DelimList = COALESCE(@DelimList + ', ', '') + Col2
from #TEMPTABLE2
where ID = @ID

return @DelimList
end

and then use it in your update:

UPDATE #TEMPTABLE1 set Col1 = CommaValues(ID)

score 0 · Answer 4 · answered Jul 28 '11 at 21:35

0

Will this work:

INSERT INTO #TEMPTABLE1 SELECT ID,COL2 FROM #TEMPTABLE2 GROUP BY ID

answered Jul 28 '11 at 21:35

Rahul

76,197
13
71
125

score 0 · Answer 5 · answered Jul 28 '11 at 21:35

You are not going to be able to issue multiple inserts in order to update a column (hence separate insert and update statements). I see two options:

Insert Then Update

INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(1,'A')
UPDATE #TEMPTABLE2 SET COL2 = COL2 + ',' + B WHERE ID = 1
UPDATE #TEMPTABLE2 SET COL2 = COL2 + ',' + C WHERE ID = 1

This is fairly messy, but works.

Insert, but Select into a comma-delimited list
In this case, you can make your primary key the combination of ID and COL2 (although I'm not sure I would recommend this) and insert the values as you are doing so above. Then, when you need the comma-delimited list, select it as such, for example, using collate (as mentioned by Pinal Dave):

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product

SQL Server - Update a column with other column values as CSV

5 Answers5