日期:2014-05-18 浏览次数:20629 次
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([name] varchar(3),[type] varchar(1)) insert [test] select 'sun','A' union all select 'cat','B' union all select 'dog','B' union all select 'mon','A' union all select 'fri','A' union all select 'red','C' declare @str varchar(50) set @str='' ;with t as( SELECT *FROM (SELECT DISTINCT [type] FROM [test])A OUTER APPLY( SELECT [name]= STUFF(REPLACE(REPLACE( ( SELECT [name] FROM [test] N WHERE [type] = A.[type] FOR XML AUTO ), '<N name="', ','), '"/>', ''), 1, 1, '') )N ) select @str=@str+';'+[type]+':'+name from t select right(@str,len(@str)-1) as value /* value ---------------------------------------- A:sun,mon,fri;B:cat,dog;C:red */
------解决方案--------------------
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] go create table [test]([name] varchar(3),[type] varchar(1)) go insert [test] select 'sun','A' union all select 'cat','B' union all select 'dog','B' union all select 'mon','A' union all select 'fri','A' union all select 'red','C' go declare @sql varchar(max) with cte as ( select [type], rusult=stuff ( ( select ';'+name from TEST a where a.type=test.type for xml path('') ) ,1,1,'' ) from TEST group by [type] ) select @sql=isnull(@sql,'')+[type]+':'+rusult+' ' from cte print @sql --A:sun;mon;fri B:cat;dog C:red
------解决方案--------------------
表a name num aa 1 bb 2 cc 4 dd 4 ee 2 一条语句实现如下: name aa,bb,cc,dd,ee DECLARE @STR VARCHAR(8000) SELECT @STR=ISNULL(@STR+',','')+name FROM (SELECT DISTINCT NAME FROM A)AS T SELECT @STR 问题描述: 无论是在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 的和(字符串相加) 1. 旧的解决方法 -- 1. 创建处理函数 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 -- 2. 新的解决方法 -- 示例数据 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 行受影响) --*/ --各种字符串分函数 --3.3.1 使用游标法进行字符串合并处理的示例。 --处理的数据 CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'b',3 --合并处理 --定义结果集表变量 DECLARE @t TABLE(col1 varchar(10),col2 varchar(100)) --定义游标并进行合并处理 DECLARE tb CURSOR LOCAL FOR SELECT col1,col2 FROM tb ORDER BY col1,col2 DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100) OPEN tb FETCH tb INTO @col1,@col2 SELECT @col1_old=@col1,@s='' WHILE @@FETCH_STATUS=0 BEGIN IF @col1=@col1_old SELECT @s=@s+','+CAST(@col2 as varchar) ELSE BEGIN INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) SELECT @s=','+C