问题描述:无论是在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)ASBEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + value FROM tb WHERE id=@id RETURN STUFF(@r, 1, 1, '')ENDGO -- 调用函数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)AOUTER 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,bb2 aaa,bbb,ccc (2 行受影响)--*/CSDN 社区帖子地址 附: 合并与分拆的CLR, sql2005的示例中有: 在安装sql 2005的示例后,默认安装目录为 drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中
用个函数实现create function fn_Code ( @Table1id int ) returns varchar(2000) as begin declare @r varchar(2000) select @r=isnull(@r+',','')+code from table2 where Table1id=@Table1id return @r end go--查询 select id,name,dbo.fn_code(id) as code from table1
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO 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 GO--合并处理函数 CREATE FUNCTION dbo.f_str(@col1 varchar(10)) RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(col2 as varchar) FROM tb WHERE col1=@col1 RETURN(STUFF(@re,1,1,'')) END GO--调用函数 SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1col1 col2 a 1,2 b 1,2,3
在安装sql 2005的示例后,默认安装目录为 drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中
@Table1id int
)
returns varchar(2000)
as
begin
declare @r varchar(2000)
select @r=isnull(@r+',','')+code from table2 where Table1id=@Table1id
return @r
end
go--查询
select id,name,dbo.fn_code(id) as code
from table1
如楼上:
详细参考:
合并分拆表_整理贴1
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
IF OBJECT_ID('TB') IS NOT NULL
DROP TABLE TB
GO
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
GO--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1col1 col2
a 1,2
b 1,2,3