SQL Server - Combine separate row values as CSV

In this post, let us see how to combine values from separate rows into delimited string.

Example 2 in this post, shows how to combine values from separate grouped records into delimited string.

Example 1:

--Examples for Combining separate row values as CSV 

DECLARE @csv TABLE (ID INT,
Name VARCHAR(30))
INSERT INTO @csv SELECT 1,'SQL Server'
INSERT INTO @csv SELECT 2,'Oracle'
INSERT INTO @csv SELECT 3,'DB2'
SELECT * FROM @csv

 
--Using COALESCE 
DECLARE @V VARCHAR(100)
SELECT @V = COALESCE(@V+', ','' )+Name FROM @csv 
SELECT @V CSV

 
--Using STUFF & XML PATH
SELECT STUFF( 
 (SELECT  ', ' + Name FROM 
   (SELECT Name FROM @csv ) AS T FOR XML PATH('')),1,2,'') AS CSV

 
--Using SUBSTRING & XML PATH
SELECT SUBSTRING(
 ( SELECT (', ' + Name)
  FROM @csv
  FOR XML PATH('')
 ) , 3,2000) 
AS CSV

Example 2:

DECLARE @csv TABLE (ID INT,
                    DBName VARCHAR(30))
INSERT INTO @csv SELECT 1,'SQL Server 2005'
INSERT INTO @csv SELECT 1,'SQL Server 2008'
INSERT INTO @csv SELECT 1,'SQL Server 2008R2'
INSERT INTO @csv SELECT 1,'SQL Server 2012'
INSERT INTO @csv SELECT 2,'Oracle 10g'
INSERT INTO @csv SELECT 2,'Oracle 11g'
INSERT INTO @csv SELECT 2,'Oracle 12c'
INSERT INTO @csv SELECT 3,'DB2 v9.7'
INSERT INTO @csv SELECT 3,'DB2 v9.8'


   
   
SELECT MAX(ID) ID,  
       STUFF((SELECT ' | ' + DBName 
              FROM   @csv X WHERE X.ID = Y.ID GROUP  BY ID,DBName
              FOR XML PATH('')), 1, 2, '') CSV                 
FROM   @csv Y 
GROUP  BY ID

See Also:

• My posts on T-SQL