Get the column values as comma separated values
Table citymaster
cityid cityname
1 A
2 B
3 C
4 D
SELECT DISTINCT STUFF((SELECT ',' + cast(s.cityid as varchar)FROM citymaster s
FOR XML PATH('')),1,1,'') AS CSV
FROM citymaster AS t
output
1,2,3,4
=========================================================
Table CompanyMaster
Table citymaster
cityid cityname
1 A
2 B
3 C
4 D
SELECT DISTINCT STUFF((SELECT ',' + cast(s.cityid as varchar)FROM citymaster s
FOR XML PATH('')),1,1,'') AS CSV
FROM citymaster AS t
output
1,2,3,4
=========================================================
Table CompanyMaster
Company Location
A Chennai
A Pune
B Delhi
B Mumbai
A Bangalore
B Hyderabad
B Kolkata
SELECT t.Company, STUFF((SELECT ',' + s.Location FROM CompanyMaster s
WHERE s.Company = t.Company FOR XML PATH('')),1,1,'') AS Locations
FROM CompanyMaster AS t GROUP BY t.Company
output:
Company Locations
A Chennai,Pune,Bangalore
B Delhi,Mumbai,Hydrabad,Kolkata
