CODEWITHSUNDEEP
sqlsql-server-2008
Joe Defill
Joe Defill

Reputation: 499

Concatenate multiple SQL rows into single column in SQL Server 2008

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.

Upvotes: 0

Views: 1878

Answers (2)

persian-theme
persian-theme

Reputation: 6638

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

Upvotes: 1

Stu
Stu

Reputation: 32614

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.

Upvotes: 0

Related Questions