SQL Server join results as comma separated list

Question

I have a company, industry, and company_industry_map many-to-many table.

company_id | company_name
1            Goldman Sachs
2            Microsoft

industry_id | industry
4             Technology
5             Finance
6             Banking

company_id | industry_id
1            5
1            6
2            4

I'd like to write a query that joins all of the industries into a comma separated list like this:

company_id | industries
1            Finance, Banking
2            Technology

Here's my general query that I'm trying to write:

SELECT company_id, 
       xxx AS industries 
  FROM company c, 
       company_industry_map m 
 WHERE c.company_id = m.company_id

You can look at this: http://stackoverflow.com/questions/8700961/using-coalesce-function-to-make-values-separated-with-commas — Rick S, Feb 28 '14 at 14:08
Specifically look at the answer(Rick S comment) with the highest score using the STUFF command — twoleggedhorse, Feb 28 '14 at 14:09
Please stop using the sql antipattern of implicit joins. They are a very POOR technique making it harder to maintain systems and leading to more risky code that can contain errors such as accidnetal cross joins that explicit syntax will not allow. — HLGEM, Feb 28 '14 at 14:38

score 12 · Accepted Answer · answered Feb 28 '14 at 14:11

You can use something like this in SQL Server

select co.CompanyID, AllIndustries = 
       (select (cast(industry as varchar(200))+',') as [text()]
       FROM company c, 
       company_industry_map m 
       WHERE c.company_id = m.company_id and c.company_id = co.company_id 
       order by industry_id for XML PATH(''))
from Companies co

score 0 · Answer 2 · answered Feb 28 '14 at 14:06

0

In SQL Server you can create a function like this:

declare @var nvarchar(max)

set @var = ''
select @var = @var  + ',' + UserLogin from [user] 

select @var

answered Feb 28 '14 at 14:06

glm

2,131
2
13
10

SQL Server join results as comma separated list

2 Answers2