SQL Server2000和2005中合并列值

最新推荐文章于 2016-07-02 00:14:00 发布

最新推荐文章于 2016-07-02 00:14:00 发布 · 75 阅读

本文介绍在SQL Server 2000及2005中如何将同一ID下的多个值聚合为一个逗号分隔的字符串。包括使用自定义函数、FOR XML AUTO以及STUFF和REPLACE函数组合的方法。

有表tb, 如下：
idvalue
—– ——
1 aa
1 bb
2 aaa
2 bbb
2 ccc

需要得到结果：
id values
—— ———–
1aa,bb
2aaa,bbb,ccc

在SQL Server2000中我们处理的方式一般都是用自定义函数去处理：
举例如下：

create function dbo.f_str(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=”
select @r=@r+’,'+value from tb where id=@id
return stuff(@r,1,1,”)
end
go

select id, values=dbo.f_str(id) from tb group by id

在SQL Server2005中，这种统计有了一种新的方法，直接使用语句调用sql2005的函数实现：
举例如下：

– 示例数据
DECLARE @t TABLE(id int, value varchar(10))
Insert @t 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(
Select DISTINCT
id
FROM @t
)A
OUTER APPLY(
Select [values]= STUFF(REPLACE(REPLACE(
(
Select value FROM @t N
Where id = A.id
FOR XML AUTO
), ‘<N value=”‘, ‘,’), ‘”/>’, ”), 1, 1, ”)
)N

/*–结果
idvalues
———– —————-
1 aa,bb
2 aaa,bbb,ccc

(2 行受影响)
–*/

最近从优快云上又发现了一种方法！
declare @a varchar(100)
select @a=coalesce(@a+’/',”)+PNAME from HPINF where perid like’207%’
select @a