SQL Server Comma Separated value among columns

Question

I want to select columns as comma-separated values by doing something like:

select column1+','+column2+','+column3+','+coulmn4 from someTable

except if any of the columns hold null values i have to skip that column from adding comma how to do this is SQL Server? [All columns are of type varchar so no casting needed]

Answer 1

Try:

select LEFT( 
      ISNULL(column1+',', '') + ISNULL(column2+',', '') + 
      ISNULL(column3+',', '') + ISNULL(column4+' ', '') , 
   len(ISNULL(column1+',', '') + 
      ISNULL(column2+',', '') + 
      ISNULL(column3+',', '') + ISNULL(column4+' ', ''))-1)
from YOUR_TABLE

Answer 2

try

Test table

create table #testCol (column1 varchar(10), column2 varchar(10),
 column3 varchar(10), column4 varchar(10))

 insert #testCol values('a', null,null,'b')
 insert #testCol values(null,'a',null,'b' )
 insert #testCol values(null,'a','Z','b' )

Query

 select isnull(column1,'')+ case when column1 is null then '' else ',' end
+ isnull(column2,'')+ case when column2 is null then '' else ',' end 
+ isnull(column3,'')+ case when column3 is null then '' else ',' end 
+ isnull(column4,'') 
 from #testCol

Output
a,b
a,b
a,Z,b

Answer 3

Can you export to csv and then strip out all the double commas?

Answer 4

Select  
  Case When Len(IsNull(Column1),'') > 0 Then Column1 + ',' Else '' End,
  Case When Len(IsNull(Column2),'') > 0 Then Column2 + ',' Else '' End,
  Case When Len(IsNull(Column3),'') > 0 Then Column3 + ',' Else '' End,
  Case When Len(IsNull(Column4),'') > 0 Then Column4 + ',' Else '' End,
  Case When Len(IsNull(ColumnN),'') > 0 Then ColumnN + ',' Else '' End
From
  SomeTable

Answer 5

select isnull(column1 + ',', '') + isnull(column2 + ',', '') + isnull(column3 + ',', '') + isnull(coulmn4, '') from someTable