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

Answer 1

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