To NULL particular fields in retrieval time in sql

Question

select isnull(column1,'')+','+isnull(column2,'')+','+isnull(column3,'') AS column4 from table  

From the above query, I am getting what I need, which is really good. But the thing here is if all the columns all NULL I am getting commas which I have used to separate the fields.

I want comma is to be replaced with NULL when every field is NULL. Can anyone help me in this? thank you!

Answer 1

You might pack the + ',' into the ISNULL()

select isnull(column1+',','')+isnull(column2+',','')+isnull(column3,'') AS column4 from table

Answer 2

You can do this using stuff() like this:

select stuff((coalesce(',' + col1, '') + 
              coalesce(',' + col2, '') +
              coalesce(',' + col3, '')
             ), 1, 1, '')

Other databases often have a function called concat_ws() that does this as well.