SQL 自定义函数实例

---------------------------------sql server多行数据拼接 ---------------------------------------
create table aaa(id int,typ varchar(20),code varchar(10))
insert into aaa values(1,'铅笔','0001')
insert into aaa values(2,'铅笔','0002')
insert into aaa values(3,'铅笔','0003')
insert into aaa values(4,'钢笔','0004')
insert into aaa values(5,'钢笔','0005')
insert into aaa values(6,'钢笔','0004')
insert into aaa values(7,'圆珠笔','0007')
insert into aaa values(8,'圆珠笔','0008')
insert into aaa values(9,'圆珠笔','0007')

----drop table aaa
-- select * from aaa
select code from aaa  where typ='铅笔'
select code from aaa  where typ='钢笔' group by code

--需求结果:
--类型    汇总
--钢笔 0004,0005
--铅笔 0001,0002,0003
--圆珠笔  0007,0008

---1.使用了游标的自定义函数
drop function GetCode

create function GetCode(@typ varchar(20))
returns varchar(30)
as
begin
 declare @code varchar(10)
 declare @temp varchar(30)
 declare @num int
 declare mycur cursor for (select code from aaa where typ=@typ group by code)
 open mycur
 fetch next from mycur into @code
 set @num=0
 while @@fetch_status=0
 begin
  if @num=0
  begin 
   set @temp=@code
  end
  else
  begin
   select @temp=@temp+'、'+@code
  end
  set @num=@num+1  
  fetch next from mycur into @code
 end
 close mycur
 deallocate mycur
 return @temp
end

select aaa.typ as 类型,dbo.GetCode(aaa.typ)as 汇总
from aaa group by aaa.typ

---2.用自定义函数做的

create function GetCode(@typ varchar(20))
returns nvarchar(200)
as
begin
 declare @str1 varchar(50)
 set @str1=''
 select @str1=@str1+'、'+code from aaa where typ=@typ group by code
 select @str1=right(@str1,len(@str1)-1)
 --或select @str1=@str1+code+'、' from aaa where typ=@typ group by code
 --select @str1=left(@str1,len(@str1)-1)
 return @str1
end

-- drop function GetCode

select typ,dbo.GetCode(typ) from aaa group by typ

-- select * from aaa

结果:
typ (无列名)
钢笔 0004、0005
铅笔 0001、0002、0003
圆珠笔 0007、0008