Tuesday, March 5, 2013

SQL SERVER – Create a Comma saparated List Using SELECT query

Scenario : 

TableName : Person
ID Name
1 Ram
2 Shyam
3 Rohit
4 Mohit
5 Shiv
6 Pinki
7 Sweta
8 Heera
9 Vimal
10 Rajesh

Desired Output
Ram,Shyam,Rohit,Mohit,Shiv,Pinki,Sweta,Heera,Vimal,Rajesh

To get this output, 

Method 1: 

SELECT ',' + Name
FROM Person
FOR XML PATH('') 

Result : ,Ram,Shyam,Rohit,Mohit,Shiv,Pinki,Sweta,Heera,Vimal,Rajesh
Method 2: 

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Person
SELECT @listStr

Result : Ram,Shyam,Rohit,Mohit,Shiv,Pinki,Sweta,Heera,Vimal,Rajesh

No comments:

Post a Comment