-------------参考例子
--建立测试环境
create table tb2 (id int,type nvarchar(10))
insert into tb2 select 1,'a'
insert into tb2 select 1,'b'
insert into tb2 select 1,'c'
insert into tb2 select 1,'d'
insert into tb2 select 2,'a'
insert into tb2 select 3,'b'
insert into tb2 select 4,'c'
insert into tb2 select 4,'d'
go
--查询处理
create function f_catString(@id int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+type from tb2 where id=@id
return(stuff(@s,1,1,''))
end
go
--调用函数
select id,dbo.f_catString(id) as type from tb2 group by id
go
CREATE PROCEDURE union_name
AS
declare @s varchar,@id as int,@name varchar ,@temp varchar,@t_id int
set @temp=""
set @t_id=0
create table #temp(id int not null primary key,name varchar(20) not null )
declare my_cusor cursor for select id,name from u_name order by id
open my_cusor
fetch next from my_cusor
into @id, @name
while @@FETCH_STATUS = 0
begin
if @id <>@t_id
begin
insert into #temp(id,name) values(@id,@name)
set @t_id = @id
end
else
begin
update #temp set name = name + @name where id=@t_id
end
fetch next from my_cusor
into @id, @name
end
close my_cusor
deallocate my_cusor
select * from #temp
GO