Friday, August 9, 2013

Comma Separated Value (CSV) in SQL Server

Comma Separated Value (CSV)


In this article I am trying to demonstrate, how to generate comma separated value (CSV) from a particular table objects fields.
So I am going to demonstrate it as step by step that we can understand it properly.

Step-1 [ The base Table Objects ]
CREATE TABLE #tbl_Student
       (Roll  INT         NOT NULLIDENTITY(1,1) PRIMARY KEY,
        Sname VARCHAR(50) NOT NULL)
Step-2 [ Inserting Records ]
INSERT INTO #tbl_Student
            (Sname)
VALUES ('Joydeep'),
       ('Sukamal'),
       ('Sangram'),
       ('Tuhin'),
       ('Sudip'),
       ('Bhola')


SELECT * FROM #tbl_Student  

Roll        Sname
1           Joydeep
2           Sukamal
3           Sangram
4           Tuhin
5           Sudip
6           Bhola 
Step-3 [ Creating CSV format ]
SELECT   SUBSTRING((SELECT ',''' + a.Sname+''''
FROM     #tbl_Student  a
ORDER BY a.Sname
FOR XML PATH('')),2,200000) As RESULT;

RESULT
'Bhola','Joydeep','Sangram','Sudip','Sukamal','Tuhin'

It is easy but very useful.