CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
cs0815
cs0815

Reputation: 17388

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).

Upvotes: 1

Views: 154

Answers (1)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

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

Upvotes: 2

Related Questions