经典行列转换例子介绍

在马可的〈行列转换 交叉表〉中一个例子

有表 t1,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成 t2:
id pid
1 1,2,3
2 1,2
3 1

典型的方法是使用一个自定义的函数
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) fromt1 where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from t1

但是在SQL Server 7.0中无法使用自定义函数，怎么办？用游标

declare cursor_t1 cursor for select distinct id from t1
declare @id int
declare @pid varchar(100)
if object_id('t2') is not null --构造t2表的结构
drop table t2
create table t2(id int,pid varchar(100))
OPEN cursor_t1
FETCH NEXT FROM cursor_t1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
set @pid =''
declare @len int
select @len=count(1) from t1 where id=@id
if object_id('tempdb..#tmp') is not null --构造临时表
drop table #tmp
select identity(int,1,1) as NewID,id,pid into #tmp from t1 where id=@id
while @len>0 --通过循环拼接字符串
begin
select @pid = @pid +','+ cast(pid as varchar) from #tmp where [NewID]=@len
set @len=@len-1
end
drop table #tmp
set @pid=reverse(right(@pid,len(@pid)-1))
insert into t2 select @id, @pid --插入结果到t2表
FETCH NEXT FROM cursor_t1 INTO @id
END
close cursor_t1
deallocate cursor_t1
select * from t2

/*
select * from t2
*/
id pid
----------- ----------
1 1,2,3
2 1,2
3 1

（所影响的行数为 3 行）

本文地址：http://www.45fan.com/a/question/71532.html