sql server 合并字段值的一般做法，共三种

sql server 合并字段值的一般做法，共三种

1、函数

drop table tb
--建测试表
create table tb(id int,name varchar(50))

--插入测试数据
insert tb
select 1,'a'
union all
select 1,'bb'
union all
select 1,'cc'
union all
select 2,'aa'
union all
select 2,'bbb'
union all
select 2,'c'

--建立函数
create function getName(@id int)
returns varchar(max)
as
begin
declare @sql varchar(max)
set @sql=''
select @sql=@sql+','+name from tb where id =@id
return stuff(@sql,1,1,'')
end
--建立查询
select id,name=dbo.getName(id) from tb group by id

select * from tb

2、游标

--建结果集
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
--定义游标
declare cursor_test cursor for
select id,name from tb order by id

--定义变量
declare @compare int,@sql varchar(max),@id int,@name varchar(50)

--进行游标的操作
open cursor_test

fetch cursor_test into @id,@name

select @compare=@id,@sql=''

while @@fetch_status=0
 begin
 if @id=@compare
 select @sql=@sql+','+@name
 else
 begin
 insert  @t values(@compare,stuff(@sql,1,1,''))
 select @sql=','+@name,@compare=@id
 end
 fetch cursor_test into @id,@name
end
insert  @t values(@compare,stuff(@sql,1,1,''))
close cursor_test
deallocate cursor_test
select * from @t

--游标用法
declare cusor_test cursor for

select value from tb where id='1'
open cusor_test
declare @r varchar(20)

fetch from cusor_test into @r
while  @@fetch_status = 0
begin
 if @r='aa'
 print('found aa')
 else
 print('fount others')
fetch next from cusor_test into @r
end
close cusor_test
deallocate cusor_test

3、outer apply + for xml auto
SELECT *
FROM(
    SELECT DISTINCT
        id
    FROM tb
)A
OUTER APPLY(
 SELECT [values]= STUFF(REPLACE(REPLACE(( SELECT name FROM tb N WHERE id = A.id FOR XML AUTO ), '<N name="', ','), '"/>', ''), 1, 1, '')
)N