开发者

SQL Server Comma Separated value among columns

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 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜