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

转载 2006年06月05日 16:37:00

无论是在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

相关文章推荐

一个循环更新某库所有表所有非主键列的值方法(sql 2005 & mysql)

1.提取某数据库中所有用户表的所有列:SELECT TOP 100 * FROM syscolumns c WHERE c.id IN (SELECT id FROM sysobjects WHERE...

SQL Server 2005中的分区表(四):删除(合并)一个分区

在前面我们介绍过如何创建和使用一个分区表,并举了一个例子,将不 同年份的数据放在不同的物理分区表里。具体的分区方式为:    第1个小表:2010-1-1以前的数据(不包含2010-1-1)。    ...
  • lary_li
  • lary_li
  • 2011年07月13日 14:42
  • 157

SQL Server2005合并字段相同的项的实例应用

if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb (id int identity(1,1...

SQL Server 2005 行号、合并、分组

--> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([djbh] varchar(10),...

SQL2005/2008中实现行转列的2种方法

CREATE TABLE sales.salesByMonth(year char(4),month char(3),amount money,PRIMARY KEY (year, month))IN...
  • zqpgood
  • zqpgood
  • 2011年11月04日 11:17
  • 978

【SQL SERVER 2005+版本行转列示例】(2012年1-8月水源槑党25强榜单新鲜出炉)

在CSDN社区 MS-SQLSERVER板块,关于行转列的问题,层出不穷,之前dawugui,roy_88等高手都多次整理过,我就不再整理了,只是给出一个示例,以供大家参考。 槑党是CSDN...

SQL Server2005中按列连接字符串三种方法

最近做一个项目,遇到一个在分组的情况下,将某一列的字段值(varchar类型)连接起来的问题,类似于sum函数对int型字段值求和。如有一个表t_table,结构和数据如图1      ...

列的范围控制 CHECK 约束 和 规则 (sql2005)

一、约束          约束定义关于列中允许值的规则,是强制完整性的标准机制。        使用约束优先于使用触发器、规则和默认值。查询优化器也使用约束定义生成高性能的查询执行计划。...

SQL Server 2005 合并行数据

--测试数据 create table tab ( NumID int, SkuID varchar(max), Stock varchar(max) ) go declare...

如何在SQL2005中循环添加列

DECLARE @i int set @i=0 WHILE @i BEGIN alter table test add lie+@i int set @i=@i+1 END 就可以添加l...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【交流】SQL 2005溢用之:合并列值
举报原因:
原因补充:

(最多只允许输入30个字)