SQL 合併紀錄

--建立測試環境
create table T1(id int, type varchar(20))
insert into T1
select 1,'A'  union all
select 1,'B'  union all
select 1,'C'  union all
select 2,'A'  union all
select 2,'D'  union all
select 3,'B'  union all
select 4,'C'  union all
select 4,'E' 
GO
--方法1. cursor
declare @t table (id int,type varchar(20))
declare @id int,@type varchar(20), @oldid int,@total varchar(20)
declare c1 cursor for
select id,type from T1 order by id
open c1
fetch next from c1 into @id ,@type
select @oldid=@id,@total=''
while @@fetch_status=0
begin
  if @id=@oldid
       select @total=@total+','+@type
  else
     begin
           insert into @t select @oldid,stuff(@total,1,1,'')
           select @oldid=@id,@total=','+@type
     end
  fetch next from c1 into @id,@type
end
insert into @t select @id,stuff(@total,1,1,'')
close c1
deallocate c1

select * from @t
GO
/******************************************************/
--方法2: function
create function dbo.fn_hebing(@id int)
returns varchar(20)
AS
begin
    declare @str varchar(20)
    set @str=''
    select @str=@str+','+type  from T1 where id=@id
   set @str=stuff(@str,1,1,'')
return @str
end
Go

select id,dbo.fn_hebing(id) from T1 group by id

--刪除測試環境
drop table T1
drop function fn_hebing