Concatenate multiple SQL rows into single column in SQL Server 2008

Question

I have 3 tables

Users

user_id  name
1        mike

Assigned_Bosses

user_id  boss_id
1        3
1        5

Bosses

boss_id  boss_email
3        foo@bar.com
5        test@bar.com

How can I select a user so I can get the emails of all bosses in single row delimited with ;?

name  boss_email
mike  foo@bar.com;test@bar.com

The following obviously won't work but it's what I'm looking to do:

SELECT concat(b.boss_email), u.name
FROM Users u
LEFT OUTER JOIN Assigned_Bosses a ON a.user_id = u.user_id
LEFT OUTER JOIN Bosses b ON b.boss_id = a.boss_id 
WHERE user_id = 1

Also this is SQL Server 2008 so not all functions are available.

Please explain why you are using an unsupported version of SQL Server. — Gordon Linoff, Aug 11 '21 at 19:43
For aggregate string concatenation in older SQL Server versions, use `FOR XML` as detailed [here](https://dba.stackexchange.com/questions/207371/please-explain-what-does-for-xml-path-type-value-nvarcharmax/207380#207380). — Dan Guzman, Aug 11 '21 at 20:59
Does this answer your question? [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Charlieface, Aug 11 '21 at 23:03

score 1 · Answer 1 · edited Aug 11 '21 at 23:27

you can use following query

SELECT
       name,
       STUFF((SELECT ';' + b.boss_email
         FROM Users u
         LEFT OUTER JOIN Assigned_Bosses a ON a.user_id = u.user_id
         LEFT OUTER JOIN Bosses b ON b.boss_id = a.boss_id 
         WHERE u.user_id = 1
         for xml path(''), TYPE).value('text()[1]', 'NVARCHAR(MAX)'),1,LEN(';'),'')
from Users
where user_id = 1

demo in db<>fiddle

score 0 · Answer 2 · answered Aug 11 '21 at 21:17

Before SQL Server had built-in string concatenation functions the traditional method to achieve the same was the for xml 'hack'.

To achieve this on your sample data you can try

select u.name, Stuff((
    select  ';' + b.boss_email 
    from Assigned_Bosses a 
    left join Bosses b on b.boss_id = a.boss_id 
    where a.user_id = u.user_id
    for xml path(''),type
).value('.','varchar(max)'),1,1,'') as Boss_Email
from users u
where u.user_id=1

Note - I don't have any means of testing on 2008 but I believe this should work.

Concatenate multiple SQL rows into single column in SQL Server 2008

2 Answers2