Query to get CSV(Comma Separated Values) from a column data

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 1065
Here is the script to get comma separated values from a column.... Most of the times we suppose to get the column values as comma separated values....

1) Get CSV format from a column of master table 
-- For Master tables
CREATE TABLE Locations1 ( LocationID INT NOT NULL PRIMARY KEY, LocationName VARCHAR(50) NOT NULL)
INSERT INTO Locations1 VALUES(1, 'Bangalore'), (2, 'Chennai'), (3, 'Hyderabad'), (4, 'Vizag')
--Sample data in the table
SELECT * FROM Locations1;
--Method1:
SELECT DISTINCT STUFF((SELECT ',' + s.LocationName FROM Locations1 s FOR XML PATH('')),1,1,'') AS CSV
FROM Locations1 AS t


--Method2: Using T-SQL
DECLARE @csv VARCHAR(MAX) =''
SELECT @csv = ',' + LocationName + @csv
FROM Locations1

SELECT STUFF( @csv, 1, 1, '') AS CSV -- To remove first extra comma

DROP TABLE Locations1

/* OUTPUT:
CSV
Bangalore,Chennai,Hyderabad,Vizag
*/

2) Get CSV format from a column of child tables
--For Child Tables
CREATE TABLE CSVGroupBY(SNo int identity, Company char(1), Location VARCHAR(30))
insert into CSVGroupBY
SELECT 'A', 'Chennai' union all
SELECT 'A', 'Pune' union all
SELECT 'B', 'Delhi' union all
SELECT 'A', 'Bangalore' union all
SELECT 'B', 'Hyderabad'

--Sample data in the table
SELECT * FROM CSVGroupBY
/*
SNo Company Location
1 A Chennai
2 A Pune
3 B Delhi
4 A Bangalore
5 B Hyderabad*/

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

--Method2:
SELECT Company, STUFF(MAX(Locations), 1, 1, '') AS CSVLocations
FROM CSVGroupBY AS extern
CROSS APPLY
(
SELECT ','+Location
FROM CSVGroupBY AS intern
WHERE extern.Company = intern.Company
FOR XML PATH('')
) pre_trimmed (Locations)
GROUP BY Company;

DROP TABLE CSVGroupBY;

/*OUTPUT:
Company roles
A Chennai,Pune,Bangalore
B Delhi,Hyderabad
*/

Comments or Responses

Posted by: Nandkishorre on: 9/2/2013 Level:Starter | Status: [Member] | Points: 10
You are Nice Mr.Chandu.

Login to post response