views:

47

answers:

5

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]

A: 

Try:

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

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

SQLMenace
Fits my need,thank you very much
Gopi
A: 

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

Beth
+5  A: 

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

Canella
Thank you very much
Gopi
A: 
select isnull(column1 + ',', '') + isnull(column2 + ',', '') + isnull(column3 + ',', '') + isnull(coulmn4, '') from someTable 
Philip Kelley