Multiple rows string Concatenation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Multiple rows string Concatenation

Author

Topic

rama.nelluru
Starting Member

37 Posts

Posted - 2014-11-13 : 11:41:15

Hi i am trying to concatenate multiple row values into single string seperated by ";" Any help could be appricated.

Table looks as below

no column1 column2 column3
1 val1 val2 val3
2 val4 val5 val6
3 val7 val8 val9

looking for result like val1 val2 val3;val4 val5 val6; val7 val8 val9

return as single string.

thanks

RAM

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 11:47:04

select ';' + column1 + column2 + column3
from table
for xml path('')

rama.nelluru
Starting Member

37 Posts

Posted - 2014-11-13 : 12:08:32

thank you..
I was trying the same statement but if val4 is null then it i snot concatenating the row that has null in the begining.

Thanks you.

RAM

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 12:16:44

Then either wrap the column names in ISNULL() or use the CONCAT() function

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-11-17 : 23:34:42

You can also try Stuff

create table #temp([no] int,[column1] varchar(100),[column2] varchar(100),[column3] varchar(100))
insert into #temp
select 1,'val1','val2','val3'
union all
select 2,'val4','val5','val6'
union all
select 3,'val7','val8','val9'

select stuff((select ';'+ISNULL(column1,'NULL')+' '+ISNULL(column2,'NULL')+' '+ISNULL(column3,'NULL') FROM #temp for xml path('')),1,1,'') as OutputString

drop table #temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources