SQL 合并多行记录的方法总汇

sql中合并多行记录的方法总汇:


–1. 创建表，添加测试数据

create table tb(id int, [value] varchar(10))

insert tb select 1, ‘aa’

union all select 1, ‘bb’

union all select 2, ‘aaa’

union all select 2, ‘bbb’

union all select 2, ‘ccc’

–select * from tb

/**//*

id value

———– ———-

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 row(s) affected)

*/


–2 在sql2000只能用自定义函数实现

—-2.1 创建合并函数fn_strsum，根据id合并value值

go

create function dbo.fn_strsum(@id int)

returns varchar(8000)

as

begin

declare @values varchar(8000)

set @values = ”

select @values = @values + ‘,’ + value from tb where id=@id

return stuff(@values, 1, 1, ”)

end

go

— 调用函数

select id, value = dbo.fn_strsum(id) from tb group by id

drop function dbo.fn_strsum

—-2.2 创建合并函数fn_strsum2，根据id合并value值

go

create function dbo.fn_strsum2(@id int)

returns varchar(8000)

as

begin

declare @values varchar(8000)

select @values = isnull(@values + ‘,’, ”) + value from tb where id=@id

return @values

end

go

— 调用函数

select id, value = dbo.fn_strsum2(id) from tb group by id

drop function dbo.fn_strsum2

–3 在sql2005/sql2008中的新解法

—-3.1 使用outer apply

select *

from (select distinct id from tb) a outer apply(

select [values]= stuff(replace(replace(

(

select value from tb n

where id = a.id

for xml auto

), ‘<n value=”‘, ‘,’), ‘”/>’, ”), 1, 1, ”)

)n

—-3.2 使用xml

select id, [values]=stuff((select ‘,’+[value] from tb t where id=tb.id for xml path(”)), 1, 1, ”)

from tb

group by id

–4 删除测试表tb

drop table tb

/**//*

id values

———– ——————–

1 aa,bb

2 aaa,bbb,ccc

(2 row(s) affected)

*/