SQL Server join results as comma separated list [duplicate]

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: stackoverflow.com/questions/8700961/… — Rick S
– Rick S, Commented Feb 28, 2014 at 14:08
Specifically look at the answer(Rick S comment) with the highest score using the STUFF command — twoleggedhorse
– twoleggedhorse, Commented Feb 28, 2014 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
– HLGEM, Commented Feb 28, 2014 at 14:38

Maryam Arshi · Accepted Answer · 2014-02-28 14:11:20Z

12

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

answered Feb 28, 2014 at 14:11

Maryam Arshi

2,0342 gold badges20 silver badges34 bronze badges

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

Comments

glm · Accepted Answer · 2014-02-28 14:06:39Z

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, 2014 at 14:06

glm

2,1612 gold badges15 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Server join results as comma separated list [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related