mssql里自定义函数的用法一例

有表 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

--建表
create table t1
(
id int,
pid varchar(10)
)

insert t1 values(10,'tom')
insert t1 values(10,'jerry')
insert t1 values(10,'aaa')
insert t1 values(10,'bbb')
insert t1 values(10,'ccc')
insert t1 values(20,'ddd')
insert t1 values(20,'eee')
insert t1 values(20,'fff')
insert t1 values(20,'ggg')
insert t1 values(20,'hhh')
insert t1 values(30,'iii')
insert t1 values(30,'jjj')
insert t1 values(30,'kkk')

select * from t1

----------------------------------------------------------------------------------------------

--1.创建一个合并的函数
if exists (select * from sysobjects where objectproperty(object_id('dbo.connections'), 'isansinullson')=1)
drop function dbo.connections
go
create function connections(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar(8000)) from t1 where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
end
go
--调用自定义函数得到结果
select distinct id,dbo.connections(id) from t1

----------------------------------------------------------------------------------------------

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

declare cursor_t1 cursor for select distinct id from t1
declare @id int
declare @pid varchar(8000)
if object_id('t2') is not null --构造t2表的结构
drop table t2
create table t2(id int,pid varchar(8000))
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=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

----------------------------------------------------------------------------------------------

--有局限的做法，要求相同的id不能超过5条记录，但纯粹使用select实现，可以借鉴理解思路

select id,pid1+pid2+pid3+pid4+pid5 pids from
(
select t3.id,
max(case t3.ranking when 1 then t3.pid else '' end) pid1,
max(case t3.ranking when 2 then ','+t3.pid else '' end) pid2,
max(case t3.ranking when 3 then ','+t3.pid else '' end) pid3,
max(case t3.ranking when 4 then ','+t3.pid else '' end) pid4,
max(case t3.ranking when 5 then ','+t3.pid else '' end) pid5
from
(select t1.id,ranking=(select count(*) from t1 t2
where t2.id=t1.id and
t2.pid<=t1.pid), pid
from t1 t1) t3
group by t3.id) t4

----------------------------------------------------------------------------------------------

--注意事项
--如果同一id拼接而成的pid长度超过256的话，请更改查询分析器里的默认字符数选项（菜单里的工具→选项→结果→每列最多字符数，默认是256），不然会造成调用自定义函数后返回值长度跟定义的返回值长度不匹配的假象。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/68303/viewspace-251590/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/68303/viewspace-251590/