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        [email protected]
5        [email protected]

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

name  boss_email
mike  [email protected];[email protected]

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
– Gordon Linoff, Commented Aug 11, 2021 at 19:43
For aggregate string concatenation in older SQL Server versions, use FOR XML as detailed here. — Dan Guzman
– Dan Guzman, Commented Aug 11, 2021 at 20:59
Does this answer your question? How to use GROUP BY to concatenate strings in SQL Server? — Charlieface
– Charlieface, Commented Aug 11, 2021 at 23:03

Charlieface · Accepted Answer · 2021-08-11 23:27:18Z

1

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

edited Aug 11, 2021 at 23:27

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

answered Aug 11, 2021 at 21:43

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stu · Accepted Answer · 2021-08-11 21:17:04Z

0

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.

answered Aug 11, 2021 at 21:17

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Collectives™ on Stack Overflow

Concatenate multiple SQL rows into single column in SQL Server 2008

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related