Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

concatenate string value in SQL

Posted by Simon Cho on 03/08/2011

Hi all,

Have you ever need a concatenation string function for group by clause?

Here is the solution.

And I like this process.


set nocount on;

if OBJECT_ID(‘test’) is not null

drop table test


create table test


i int identity(1,1)

,c varchar(255)



insert into test (c)

select ‘a’

union all

select ‘b’

go 100

select i%2, replace(replace(max(b.list),,),,) as sum_c

from test a

cross apply (

select c + ‘,’ as c

from test

where i%2 = a.i%2

for xml path()

) b (list)

group by a.i%2



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s