关闭

【交流】SQL 2005溢用之:合并列值

标签: sqlxmlinsertfunctionobjecttable
2080人阅读 评论(0) 收藏 举报
分类:

无论是在sql 2000, 还是在 sql 2005 中,都没有提供字符串的聚合函数, 所以, 当我们在处理下列要求时,会比较麻烦:

有表tb, 如下:
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc

需要得到结果:
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc

即, group by id, 求 value 的和(字符串相加)

这个问题的一般处理方法是, 写一个聚合函数:
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


这样的问题是,函数不通用,必须为每个要处理的表编写相应的处理函数
在SQL2005中,这个问题的更好解决办法是写一个CLR函数,用于实现字符串的聚合,这样可以解决通用性的问题了。

 

而在我下面要实现的, 是只用一条SQL语句来完成这个功能
(绝对是一条, 不会是用EXEC()取巧的那种)

-- 示例数据
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

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc

(2 行受影响)
--*/

 

SQL 2005新增了xml数据类型, 而且xml数据类型可以方便的与字符类型之间做转换,上面的方法只是巧妙地利用了这一点,结合字符串的一些处理函数就出来结果了

-- 下面这个示例也是合并字符串的, 以系统表为列, 合并两列

SELECT *
FROM(
SELECT DISTINCT
type
FROM sys.objects
)O
OUTER APPLY(
SELECT names = STUFF(REPLACE(REPLACE(
(
SELECT object_id, name FROM sys.objects N
WHERE type = O.type
FOR XML AUTO
), '<N ', ','), '/>', ''), 1, 1, '')
)N

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:262648次
    • 积分:2993
    • 等级:
    • 排名:第12144名
    • 原创:62篇
    • 转载:53篇
    • 译文:0篇
    • 评论:50条
    最新评论