Two Approaches for getting Comma Separated Values for each ID

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 403
DECLARE @CSV TABLE(SNo  int identity, Company char(1),  Location  VARCHAR(30))
insert into @CSV
SELECT 'A', 'Chennai' union all
SELECT 'A', 'Pune' union all
SELECT 'B', 'Delhi' union all
SELECT 'A', 'Bangalore' union all
SELECT 'B', 'Hyderabad'

-- Get CSV values
SELECT t.Company, STUFF((SELECT ',' + s.Location FROM @CSV s WHERE s.Company = t.Company FOR XML PATH('')),1,1,'') AS CSV
FROM @CSV AS t
GROUP BY t.Company

-- 2nd Approach
SELECT Company, STUFF(MAX(roles), 1, 1, '') AS roles
FROM @CSV AS extern
CROSS APPLY
(
SELECT ','+Location
FROM @CSV AS intern
WHERE extern.Company = intern.Company
FOR XML PATH('')
) pre_trimmed (roles)
GROUP BY Company;

/*OUTPUT:
Company Location
A Chennai,Pune,Banglore
B Delhi,Hyderabad */

Comments or Responses

Login to post response