0

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.

Improve this question
4
  • 1
    Please explain why you are using an unsupported version of SQL Server. Commented Aug 11, 2021 at 19:43
  • I don't really have a choice Commented Aug 11, 2021 at 19:49
  • 1
    For aggregate string concatenation in older SQL Server versions, use FOR XML as detailed here. Commented Aug 11, 2021 at 20:59
  • Does this answer your question? How to use GROUP BY to concatenate strings in SQL Server? Commented Aug 11, 2021 at 23:03

2 Answers 2

Reset to default
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

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

Comments

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.