sqlserver中的列数据转换为字符串

[coderee]

实现表中的按A字段汇总，B字段形成逗号隔开的字符串，有哪位知道该如何实现？

[AcHerat]

SQL版本如果是2005及其之上的，参考 xml 的应用， for xml path 的用法！

[coderee]

sqlserver2000有办法吗？

[Tiger_Zhao]

只能自己写个函数：遍历以A字段为条件的游标，然后一个个地把B字段拼起来。

[AcHerat]

create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

create function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id
  return @str
end
go

--调用函数
select id , value = dbo.f_str(id) from tb group by id

drop function dbo.f_str
drop table tb

[AcHerat]

什么意思？单字段？ select @str = isnull(@str + ',' , '') + cast(b as varchar) + ' | ' + cast(c as varchar) ... from tb where id = @id

[coderee]

declare @string varchar(100) set @string='' select @string=@string+','+cast(lch as varchar) from ww1.dbo.lsee select STUFF(@string,1,1,'') 这个只能实现单个字段的转换 游标如何写？

[AcHerat]

create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
    if @id = @id_old
       select @s = @s + ',' + cast(@value as varchar)
    else
      begin
        insert @t values(@id_old , stuff(@s,1,1,''))
        select @s = ',' + cast(@value as varchar) , @id_old = @id
      end
    fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursor

select * from @t
drop table tb

[阳泉酒家小当家]

引用 5 楼 coderee 的回复:

declare @string varchar(100) set @string='' select @string=@string+','+cast(lch as varchar) from ww1.dbo.lsee select STUFF(@string,1,1,'') 这个只能实现单个字段的转换 游标如何写？

为啥要用游标写呢，游标的效率是比较低的