Somashekhar Somashekhar - 4 months ago 7
SQL Question

How to display SQL Server Table values in Comma Separated Values

Table - tabtest

Col1 Col2 Col3
abc NULL xyz
NULL NULL mno
NULL pqr stuv
def lmn NULL


How to display comma separated values like

OUTPUTX
abc,xyz
mno
pqr,stuv
def,lmn


Below is my query, which displays correct, however if cell value contains comma, it gives wrong

SELECT REPLACE(REPLACE(REPLACE(ISNULL(LTRIM(RTRIM(col1)),' ')+',' + ISNULL(LTRIM(RTRIM(col2)),' ')+ ','+ISNULL(LTRIM(RTRIM(col3)),' '),',,',','),' ,',''),', ','') outputx from [tabtest]


Please help

Answer

Demo Here

;With cte
as
(
    select 
    col1+',' as col1,
    case when col3 is null or col2 is null 
         then col2 else col2+',' 
    end as col2,
    col3 as col3
    from t1
)
Select CONCAT(col1,col2,col3) from cte

Nulls make it difficult to provide a generic solution for N columns.When there are no nulls,you can simply do like

 select CONCAT(col1,','col2,',',col3) from table