Converting Multiple Rows into a CSV String (Set Based Method)

original code for Converting Multiple Rows into a CSV String (Set Based Method)
http://www.sqlteam.com/article/converting-multiple-rows-into-a-csv-string-set-based-method


create table #Page47 (
i int not null,
vc varchar(5) not null,
constraint pk_Page47 primary key (i,vc) )
go
set nocount on
declare @i int
select @i = 0
while @i <5000
begin
insert into #Page47 (i,vc)
select round((rand() * 100), 0),
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97) +
char(round((rand() * 25), 0) + 97)
select @i = @i + 1
end
go
--create a table to work with
create table #workingtable (
i int not null,
vc varchar(5) not null,
list varchar(8000),
constraint pk_wt primary key (i,vc) )
insert into #workingtable (i,vc)
select i,vc
from #Page47
order by i,vc
declare
@list varchar(8000),
@lasti int
select
@list = '',
@lasti = -1
--here is the meat of the work
update
#workingtable
set
@list = list = case
when @lasti <> i then vc
else @list + ', ' + vc
end,
@lasti = i
--return a sample from the final rowset
select top 10
i,
case
when len(max(list)) > 50 then convert(varchar(50), left(max(list),47) + '...')
else convert(varchar(50),max(list))
end as list
from
#workingtable
group by
i
order by
newid()
go
i list
----------- --------------------------------------------------
127 itvgq, ljosw, nxmdj, oshrp, plxff, pubig, sthck...
849 gcifo, hbxkf, njkdl, sfesm, sjhky, uxhfq, vjeno...
684 fejly, fqyqf, gpfce, hutht, kwywo, mapco, momqn...
461 fsofv, fzked, murat, vzmek, yrqjo
612 nmmey, tfjhv, ulwuj, xxaaq
374 bbthd, jvjwz, klcsq, mrakf, peztf, pixww, rtwdd
730 dlynf, egqei, hhckx, nsvdn, obnhh, rfbwh, ytgfi
458 eijdr, gtxhu, lhtqh, phprf, qjhcr, vqnos
655 bijer, fwlgk, nrcbm, sohho, trjtw, usjdj, uvpie...
837 ayxcv, epurf, flvtj, ftxcj, imjap, pmygd, sqhcc...