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]
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
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
Can you export to csv and then strip out all the double commas?
select isnull(column1 + ',', '') + isnull(column2 + ',', '') + isnull(column3 + ',', '') + isnull(coulmn4, '') from someTable
