Concatenate multiple rows with an update statement

Question

I have a temp table variable that I need to update with something sort of like a comma delimited string.

declare @clients table (clientid int, hobbylist char(1000))
declare @hobbies table (clientid int, hobbynumber smallint)

insert into @clients values (3, '')
insert into @clients values (5, '')
insert into @clients values (12, '')

insert into @hobbies values (3, 4)
insert into @hobbies values (3, 5)
insert into @hobbies values (3, 7)
insert into @hobbies values (12, 3)
insert into @hobbies values (12, 7)

So @clients contains:

clientid     hobbylist
---------------------------
3
5
12

And @hobbies contains:

clientid     hobbylist
---------------------------
3            4
3            5
3            7
12           3
12           7

I need to update the @clients table so that it contains:

clientid     hobbylist
---------------------------
3            4;;5;;7
5
12           3;;7

The closest thing that I found was this: How to concatenate multiple rows? But I can't understand how he is getting "one, two, three" looking at the what is marked as the correct answer.

I'm using SQL Server 2008 R2 64. I tried using a cursor but it was slow (and there will be tons of this type of thing in this sp).

What's the most efficient way to do this?

Edit:

From njk's suggestion, I tried this:

update c
set hobbylist=Stuff((
            Select  ';;' + cast(hobbynumber as char)
            From    @hobbies h
            Where   h.clientid = c.clientid
            For             XML Path('')
    ), 1, 1, '')
from @clients c

It didn't give me an error, but the result is so wacked, I can't find a good way to show it here.

e.g. Hobby list for client 3 looks like this:

;4                             ;;5                             ;;7

ALMOST works. Don't know where the spaces are coming from.

Edit 2.

Duh. I'm using cast. I need to trim the thing. This solution works for me on my server. I'm going to see if I can get ClearLogic's working, as well, so I can mark it as a correct answer.

Duplicate [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) — Kermit, Sep 05 '12 at 23:39
Checking that out now, njk! (You're my hero if this is the solution.) — Julie, Sep 05 '12 at 23:42
You just need to replace the comma with a double semi-colon. — Kermit, Sep 05 '12 at 23:42
But can I use all the "Stuff" stuff in an update? e.g. update c set @hobbylist = Stuff(( etc.)) - I tried that, going off another example, and it just got erros. (This obviously ain't my forte, and I have about a total of 5 hours into SQL 2008, coming from 2000.) — Julie, Sep 05 '12 at 23:45

score 2 · Accepted Answer · answered Sep 06 '12 at 00:08

2

Here you go

       ;WITH CTE AS 
         (SELECT DISTINCT  clientid,hobbylist= STUFF((SELECT ';'+ LTRIM(STR(hobbynumber))
          FROM @hobbies yt
           WHERE yt.clientid = sc.clientid            
         FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
         FROM @hobbies AS sc 
         )
         UPDATE a
         SET a.hobbylist=b.hobbylist 
         FROM @clients AS a
         INNER JOIN CTE AS b
         ON a.clientid = b.clientid

answered Sep 06 '12 at 00:08

ClearLogic

3,616
1
23
31

Unfortunately, I got an error. (I'm learning all sorts of new 2008 errors.) UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations No clue what that means. – Julie Sep 06 '12 at 00:13
are you working in ado.net? did you tried-> SET ARITHABORT ON – ClearLogic Sep 06 '12 at 00:27
I added 'set arithabort on' to the top of the file, and it works. – Julie Sep 06 '12 at 00:29
Checked that out as you were typing. It works if I set it on at the top of the file. (2000 doesn't have that setting.) This is an SQL stored procedure that I will set up as a job to write a delimited file to an SFTP folder for a marketing application to pick up. Thanks! – Julie Sep 06 '12 at 00:31

score 0 · Answer 2 · answered Sep 06 '12 at 04:49

try this>

update c SET c.hobbylist = e.list  from @clients c inner join 
(select clientid,STUFF((select ';;'+cast(hobbynumber as varchar(10))from @hobbies h where h.clientid = c.clientid for xml path('')),1,2,'') as list
from @hobbies c
group by clientid) e
on c.clientid = e.clientid 

select * from @clients

Concatenate multiple rows with an update statement

2 Answers2