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.

http://www.projectdmx.com/tsql/rowconcatenate.aspx

And I like this process.

Ex)

set nocount on;

if OBJECT_ID(‘test’) is not null

drop table test

go

create table test

(

i int identity(1,1)

,c varchar(255)

)

go

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

go

 

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: