CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Ashish Gupta
Ashish Gupta

Reputation: 15139

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

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

Upvotes: 2

Views: 598

Answers (5)

etliens
etliens

Reputation: 1342

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]

Upvotes: 7

TimothyAWiseman
TimothyAWiseman

Reputation: 14873

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

Upvotes: 3

CristiC
CristiC

Reputation: 22708

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)

Upvotes: 1

Bobby D
Bobby D

Reputation: 2159

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

Upvotes: 0

Rahul
Rahul

Reputation: 77926

Will this work:

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

Upvotes: 0

Related Questions