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
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
No comments:
Post a Comment