SQL server使用自定义函数以及游标
编号 | 标准宗地编码(landCode) | 所在区段编码(sectCode) |
1 | 131001BG001 | G001 |
2 | 131001BG002 | G001 |
3 | 131001BG003 | G001 |
4 | 131001BG004 | G002 |
5 | 131001BG005 | G003 |
现在需要将表中的数据转换为如下表所示结果:
编号 | 区段编码 | 包含的标准宗地 |
1 | G001 | 131001BG001,131001BG002,131001BG003 |
2 | G002 | 131001BG004 |
3 | G003 | 131001BG005 |
代码如下:
create function combstr(@name nvarchar(50)) returns nvarchar(300) as begin declare @resultStr nvarchar(300) declare @tempStr nvarchar(500) declare @flag int declare myCur cursor --定义游标 For(select landCode from land where sectCode=@name ) open myCur C-打开游标 fetch next from myCur into tempStr C将游标下移 set @flag=0 while @@fetch_status=0 begin if @flag=0 begin set @resultStr=@tempStr end else begin set @resultStr=@resultStr+','+@tempStr end set @flag=@flag+1 fetch next from myCur into @tempStr end close myCur deallocate myCur return @result end
相关推荐
zycchun 2020-10-16
liuyang000 2020-09-25
talkingDB 2020-06-12
LuoXinLoves 2020-06-06
Justdoit00 2020-04-26
lt云飞扬gt 2020-04-25
流云追风 2020-04-22
lt云飞扬gt 2020-04-21
yuanshuai 2020-03-06
dreamhua 2020-02-21
ALiDan 2020-02-18
dreamhua 2020-01-31
whyname 2019-12-29
tanrong 2019-12-17
暗夜之城 2019-11-13
liuyang000 2019-11-01
dreamhua 2019-10-28
廖金龙 2016-01-04