Hi Friends

How to write a query to achieve the following task in TSQL:

Col_1  Col_2  Col_3  Col_4

  A           B        NULL     D

  A           B          C       NULL

To        A, B, C

            A, B, C

I wrote a query like this: select Col_1+','+Col_2+','+Col_3+','+Col_4 from table

and I'm getting result like this: A, B, , D (as Col_3 is NULL)

        A, B, C, (getting comma after C as Col_4 is

create table test( Col_1 char(1), Col_2 char(1), Col_3 char(1), Col_4 char(1))
insert into test values('a','b', null,'d')
,('a','b', 'c', null)



select concat(col_1,','+col_2,','+col_3, ','+col_4)
--or
,col_1 +isnull(','+col_2,'')+isnull(','+col_3,'')+ Isnull(','+col_4,'')  from test

drop table test

• Proposed as answer by Praveen Rayan D'sa 7 hours 10 minutes ago

Try below code,

Declare @t table(col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 varchar(10))

Insert Into @t Values('A','B','C','D'),(Null,'B','C','D'),('A',Null,'C','D'),('A','B',Null,'D'),('A','B','C',Null)

Select	Case When col4 Is Not Null Then
		IsNull(col1 + ',','') + IsNull(col2 + ',','') + IsNull(col3 + ',','') + IsNull(col4,'') 
		Else
		IsNull(col1 + ',','') + IsNull(col2 + ',','') + IsNull(col3 ,'') + IsNull(col4,'') 
		End As Result
From	@t

Hope it works.

• Proposed as answer by Felix Pamittan 5 hours 24 minutes ago

select Col_1 +
COALESCE(','+Col_2,'') +
COALESCE(','+Col_3,'') + 
COALESCE(','+Col_4 ,'')
from table

If Col_1 is also nullable use

SELECT CASE WHEN Col_1 IS NOT NULL 
THEN 
Col_1 +
COALESCE(','+Col_2,'') +
COALESCE(','+Col_3,'') + 
COALESCE(','+Col_4 ,'') 
ELSE
STUFF(
COALESCE(','+Col_2,'') +
COALESCE(','+Col_3,'') + 
COALESCE(','+Col_4 ,''),1,1'')
END 
FROM table

If you use SQL Server 2012 and onwards use new function called CONCAT which is already treats the NULLs