[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

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,
0
andrishelp
Asked:
andrishelp
2 Solutions
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now