csetzkorn csetzkorn - 5 months ago 14
SQL Question

Concatenate several columns as comma-separated string

The following is a starting point to concatenate several columns to one string where the values are comma separated. If the column entry is empty or NULL no comma should be used:

IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp;

CREATE TABLE #Temp
(
Id INT,
Name1 NVARCHAR(10) ,
Name2 NVARCHAR(10) ,
Name3 NVARCHAR(10)
);

INSERT INTO #Temp
SELECT 1,
N'Name1' ,
NULL ,
N'Name3'
UNION
SELECT 2,
N'Name1' ,
N'Name2' ,
N'Name3'
UNION
SELECT 3,
NULL ,
NULL ,
N'Name3'
UNION
SELECT
4,
N'' ,
N'' ,
N'Name3';

SELECT Id, STUFF(COALESCE(N',' + Name1, N'') + COALESCE(N',' + Name2, N'')
+ COALESCE(N',' + Name3, N''), 1, 1, '') AS ConcateStuff
FROM #Temp;


The current results are as follows:

Id ConcateStuff
1 Name1,Name3
2 Name1,Name2,Name3
3 Name3
4 ,,Name3


Everything work fine for NULL entries but not for empty entries. The last row's result should just be:

Name3


Is there a simple way to get this to work without using complex nested case statements (ultimately I have to concatenate more than 3 columns).

Answer

By using NULLIF you can achieve it.

SELECT  Id, STUFF(COALESCE(N',' + NULLIF(Name1, ''), N'') + COALESCE(N',' + NULLIF(Name2, ''), N'')
              + COALESCE(N',' + NULLIF(Name3, ''), N''), 1, 1, '') AS ConcateStuff
FROM    #Temp;

Result

Id  ConcateStuff
-----------------
1   Name1,Name3
2   Name1,Name2,Name3
3   Name3
4   Name3
Comments