利用游标合并字符

create table tb (col1 nvarchar(50),col2 nvarchar(10))

insert tb select 'a','1'
union all select 'a','2'
union all select 'a','3'
union all select 'b','1'
union all select 'c','2'
union all select 'c','1'
union all select 'b','2'

select * from tb

-----declare cursor and merge data
declare @t table(col1 nvarchar(50),col2 nvarchar(100))
declare tb cursor local  for select col1,col2 from tb order by col1,col2

--declare @col1_old
declare @col1_old nvarchar(50),@col1 nvarchar(10), @col2 nvarchar(10),@s nvarchar(100)
--open cursor
open tb
fetch tb into @col1,@col2
select @col1_old = @col1,@s=''
while @@fetch_status =0
begin
      if @col1 = @col1_old  --当前的列与上次读取的列进行比较
         select @s=@s+','+cast(@col2 as nvarchar(10))
      else
         begin
         insert @t values(@col1_old,stuff(@s,1,1,''))
         select @s=','+cast(@col2 as varchar),@col1_old=@col1
         end
fetch tb into @col1,@col2
end 
insert @t values(@col1_old,stuff(@s,1,1,''))
close tb
deallocate tb

select *from @t