sql - Add selected values from multi column into one column separated by ',' -

i want select values multiple columns 1 column. have 2 separate columns want 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)         tbl1   join      tbl2 on....

i not able desired output. concat not recognized built in function name.

you use + operator , cast zip field varchar directly this:

for example:

select 'dara singh' + char(13) + '1234 main street' + ',' + 'ny' + ','          + cast(95825 varchar(10))

this how query look:

select name + char(13) + [address] + ',' + [state] + ',' + cast([zip] varchar(10)) tbl1 join tbl2 on....

Script