1

I want to select values from multiple columns into one column. I have 2 separate columns from which i want to get name,address,state,zip in following format in SQL Server 2008

Name (new line)

address,state,zip

Name (new line)

address,state,zip

Query:

select 
    name + char(13) + concat(address,',', state,',', zip)   
from 
    tbl1  
join 
    tbl2 on....

I am not able to get the desired output. I get concat is not a recognized built in function name.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3399326
  • 863
  • 4
  • 13
  • 24
  • Have you tried using [Coalesce](https://msdn.microsoft.com/en-us/library/ms190349.aspx)? – John Odom Jun 03 '15 at 20:59
  • 2
    Don't format data in SQL... That's what your APP/UI is for. and concat is new to SQL 2012 not in 2008. in 2008 just use + operator See-->http://stackoverflow.com/questions/10550307/how-do-i-use-the-concat-function-in-sql-server-2008-r2 – xQbert Jun 03 '15 at 21:01
  • name + char(13) + address + ',' + state + ',' + zip does not work? You may of course need to wrap the columns with ISNULL(col,'') – Hozikimaru Jun 03 '15 at 21:03
  • so, it is not a better idea to format even if I am writing stored procedures and display in grid? – user3399326 Jun 03 '15 at 21:26
  • `CONCAT` is a **new feature** in SQL Server **2012** - it's just not available in the 2008 version yet – marc_s Jun 04 '15 at 02:41

1 Answers1

0

You could use + operator and cast the zip field as varchar directly like this:

For example:

select 'Dara Singh' + char(13) + '1234 Main Street' + ',' + 'NY' + ',' 
        + cast(95825 as varchar(10))

This is how your query would look:

select name + char(13) + [address] + ',' + [state] + ',' + cast([zip] as varchar(10))
from tbl1 join tbl2 on....
FutbolFan
  • 13,235
  • 3
  • 23
  • 35
  • thanks nepali rookie. I had to use rtrim() to format but new line character char(13) doesn't seem to work – user3399326 Jun 03 '15 at 21:22
  • As far as I am aware, there is no way to do a carriage return line break unless you want to print those values. In that case, you just do `Print 'Dara Singh' + char(13) + '1234 Main Street' + ',' + 'NY' + ',' + cast(95825 as varchar)` – FutbolFan Jun 03 '15 at 21:26
  • use char(13)+char(10) See-->http://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string – xQbert Jun 04 '15 at 12:45
  • @xQbert This is only possible if you are doing a PRINT. And, I believe he is looking for line break in the resultset. – FutbolFan Jun 04 '15 at 13:00