create table #T1
(
A varchar(10),
B varchar(20)
)
insert into #T1 values ('aa','1')
insert into #T1 values ('aa','9a')
insert into #T1 values ('bb','1')
insert into #T1 values ('bb','10')
insert into #T1 values ('bb','16')
insert into #T1 values ('aa','16')
insert into #T1 values ('aa','17')
insert into #T1 values ('aa','30')
insert into #T1 values ('bb','6df')
insert into #T1 values ('aa','5')
insert into #T1 values ('aa','8')
insert into #T1 values ('aa','ed')
所要的结果:
aa 1,9a,16,17,30,5,8,ed
bb 1,10,16,6df
解决方法:
declare @c varchar(1024)
set @c=''
declare @x char(10),@y char(10)
set @x=''
set @y=''
select @y=@x,@x=x.a,@c=@c + (case @x when @y then ',' else ';' + x.a + ':' end)
+x.d from (select b as d,a from #t1) as x order by x.a
set @c=substring(@c,2,len(@c)-1)
select @c
为什么说是高效呢?
因为摒弃了游标和函数的途径,而采用变量的方式来保存值
也就是说避免了游标和函数自身的缺点