SQL server - SELECT Query to concat the few columns

SELECT col1, col2,col3,col4,col5 FROM tablename

All columns data type are integer

I wrote this following statement, but any of the column has NULL value, I get empty string which is incorrect result.

SELECT (convert(nvarchar(10),col1) + ',' +convert(nvarchar(10),col2)+ ',' +convert(nvarchar(10),col3)+ ',' +convert(nvarchar(10),col4)+ ',' +convert(nvarchar(10),col5)) as colstring FROM tablename


How to concat all columns and make it one string? If any of the column value is NULL, do not include that?
Thanks,
andrishelpAsked:
Who is Participating?
 
lluddenCommented:

SELECT CAST(ISNULL(col1,'') as nvarchar(10)) + CAST(ISNULL(col2,'') as nvarchar(10)) + CAST(ISNULL(col3,'') as nvarchar(10)) + CAST(ISNULL(col4,'') as nvarchar(10))
FROM tablename
0
 
hyphenpipeCommented:
SELECT (convert(nvarchar(10),isnull(col, '')1) + ',' +convert(nvarchar(10),isnull(col2,''))+ ',' +convert(nvarchar(10),isnull(col3, ''))+ ',' +convert(nvarchar(10),isnull(col4, ''))+ ',' +convert(nvarchar(10),isnull(col5, ''))) as colstring FROM tablename
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.