无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，所以，当我们在处理下列要求时，会比较麻烦：有表tb, 如下：id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结果：id values------ -----------1 aa,bb2 aaa,bbb,ccc即， group by id, 求 value 的和（字符串相加）
1. 旧的解决方法-- 1. 创建处理函数CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGINDECLARE @r varchar(8000)SET @r = ''SELECT @r = @r + ',' + valuefrom tbWHERE id=@idRETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数
SELECt id, values=dbo.f_str(id)from tbGROUP BY id
-- 2.1 新的解决方法-- 示例数据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 DISTINCTidfrom @t)AOUTER APPLY(SELECT[values]= STUFF(REPLACE(REPLACE((SELECT value from @t NWHERE id = A.idFOR XML AUTO), '', ''), 1, 1, ''))N
/*--结果id values----------- ----------------1 aa,bb2 aaa,bbb,ccc(2 行受影响)--*/--2.2DECLARE @TB TABLE([Name] VARCHAR(1), [Value] VARCHAR(6))INSERT @TBSELECT 'A', '123' UNION ALLSELECT 'A', '677' UNION ALLSELECT 'B', 'HHDA' UNION ALLSELECT 'B', 'JYUKY' UNION ALLSELECT 'B', 'WRWFCW' UNION ALLSELECT 'B', 'YUYUY' UNION ALLSELECT 'C', 'TRREER'
SELECT [Name],STUFF((SELECT ','+[Value] from @TB WHERE NAME=A.NAME FOR XML PATH('')),1,1,'') AS [Value]from @TB AS AGROUP BY [Name]/*Name Value---- ------------------------------------------A 123,677B HHDA,JYUKY,WRWFCW,YUYUYC TRREER*/
--各种字符串分函数
--3.3.1 使用游标法进行字符串合并处理的示例。--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3
--合并处理--定义结果集表变量DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
--定义游标并进行合并处理DECLARE tb CURSOR LOCALFORSELECT col1,col2 from tb ORDER BY col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SE
