Concatenation of Strings - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Concatenation of Strings

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-03-10 : 16:58:30

Guys,

I have following table where I need to concatenate varchar column.

For example

ID COMMENT
__________________
1 JOHN SMITH
1 SURRENDER
1 TO COPS

I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-03-10 : 18:06:41

This may help...

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 21:22:45

and also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

quote:
I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1

You will need another column to determine the order of concatenation or you might end up as "SURRENDER TO COPS JOHN SMITH"

KH
[spoiler]Time is always against us[/spoiler]

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-11 : 00:34:30

Hi,

Try with this

Declare @StrConcat TABLE (col1 nvarchar(10),col2 nvarchar(10))

insert into @StrConcat
select 'db1','host1' union all
select 'db1','host2'union all
select 'db1','host3'union all
select 'db2','host1'union all
select 'db2','host2'union all
select 'db3','host2'union all
select 'db3','host3'

select col1, stuff( ( select ','+ col2 from @StrConcat t1 where t2.col1 = t1.col1 for xml path('')),1,1,'')
from @StrConcat t2
group by col1
order by col1

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-03-11 : 15:27:36

Thanks for the reply, when I try to group by multiple columns I get an error using XML path is there any way round it

SELECT id, todate
STUFF((SELECT ' ' + comment
FROM TEST2 a
WHERE a.ID = b.ID and a.todate = b.todate
FOR XML PATH(''))
,1,1,'')AS String
FROM TEST2 b
GROUP BY id, todate

I get the following error message

"Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'FOR'.
"

Is there any way round it

Thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-03-11 : 16:26:58

To a hammer, everything looks like a nail. XML is not the answer here.

Take a look at the link in the first post.
Take a look at this article: http://www.sqlteam.com/article/converting-multiple-rows-into-a-csv-string-set-based-method
....and more importantly, read the comments on the article where some great suggestions are made.
Event better, write a CLR user-defined aggregate. I've found, at least in 2K5 that to be the best performer.

Jay
to here knows when

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources