SQL Server的逗号分隔列中的值

Question

我想要做这样的事情，选择以逗号分隔值的列：

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

除非任何列持有空值我必须从添加逗号跳过列 如何做到这一点是SQL Server？ [所有列是varchar类型所以没有铸造所需的]

Answer 1

尝试

测试表

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') 

查询

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 

输出
A，B
A，B
a，Z，b

Answer 2

你可以输出到csv，然后去掉所有的双逗号？

Answer 3

 

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 4

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