Friday 6 April 2012

SQL Server : Comma seperated values from table column - Multiple Solutions

In some cases we need to get comma separated or any user defined separator values from table column.
There are multiple solutions for that.

Here is example :
Create one temp table and insert some records.
This table is two column one sport_name and one identity column.
    Create table #TestSport (sport_name varchar(max), id int not null identity) on [Primary]
    GO
    INSERT INTO #TestSport (sport_name) VALUES ('cricket')
    INSERT INTO #TestSport (sport_name) VALUES ('hockey')
    INSERT INTO #TestSport (sport_name) VALUES ('football')
    INSERT INTO #TestSport (sport_name) VALUES ('baseball')
   
    GO

Now if we want comma seperated value of sport_name columns.
Here are some scripts.

-- Get CSV values using SUBSTRING Function

SELECT SUBSTRING(
(SELECT ',' + s.sport_name
FROM #TestSport s
ORDER BY s.sport_name
FOR XML PATH('')),2,200000) AS CSV_USING_SUBSTRING

Output : 



-- Get CSV values Using STUFF Function

SELECT STUFF(
(SELECT ',' + s.sport_name
FROM #TestSport s
ORDER BY s.sport_name
FOR XML PATH('')),1,1,'') AS CSV_USING_STUFF

Output : 


-- Get CSV values Using COALESCE Function

DECLARE @Csv varchar(Max)
SELECT @Csv= COALESCE(@Csv + ',', '') +
CAST(s.sport_name AS varchar(50))
FROM #TestSport s
ORDER BY s.sport_name
SELECT @Csv as CSV_USING_COALESCE

Output : 

2 comments:

  1. Hi Jayesh,
    Good one but you can have following way also.

    DECLARE @CSVName VARCHAR(MAX)
    SELECT @CSVName=ISNULL(@CSVName+',','') + Name FROM tblStaff
    PRINT @CSVName

    ReplyDelete
    Replies
    1. Hi Vimal,
      This is also a good way to get comma separated values.
      Keep visiting blog and give your valuable feedback.

      Delete