- -- 表结构
- select * from t;
- go
- id
- 0
- 1
- 1
- 5
- -- 用游标自定义的函数
- alter function fn_getName()
- returns varchar(255)
- as
- begin
- declare @name varchar(50) ,
- @strname varchar(255)
- declare cur cursor for select id from t;
- set @strname=''
- open cur
- fetch next from cur into @name
- while @@fetch_status=0
- begin
- set @strname=@strname+@name
- fetch next from cur into @name
- end
- Close cur
- return @strname
- end
- go
- -- 结果如下
- select dbo.fn_getName()
- go
- (无列名)
- 0115
- -- 用for xml path将列拼字符串
- select ','+cast(id as varchar) from t for xml path('')
- go
- XML_F52E2B61-18A1-11d1-B105-00805F49916B
- ,0,1,1,5
- -- 去除最前的逗号
- select stuff((select ','+cast(id as varchar) from t for xml path('')),1,1,'')
- go
- (无列名)
- 0,1,1,5