--带符号合并行列转换--有表t,其数据如下: a b 1 1 1 2 1 3 2 1 2 2 3 1 --如何转换成如下结果: a b 1 1,2,3 2 1,2 3 1 create table tb ( a int, b int ) insert into tb(a,b) values(1,1) insert into tb(a,b) values(1,2) insert into tb(a,b) values(1,3) insert into tb(a,b) values(2,1) insert into tb(a,b) values(2,2) insert into tb(a,b) values(3,1) goif object_id('pubs..f_hb') is not null drop function f_hb go--创建一个合并的函数 create function f_hb(@a int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(b as varchar) from tb where a = @a set @str = right(@str , len(@str) - 1) return(@str) End go--调用自定义函数得到结果: select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果 a b ----------- ------ 1 1,2,3 2 1,2 3 1(所影响的行数为 3 行)
问题描述:无论是在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 行受影响) --*/附: 合并与分拆的CLR, sql2005的示例中有: 在安装sql 2005的示例后,默认安装目录为 drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
goif object_id('pubs..f_hb') is not null
drop function f_hb
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1(所影响的行数为 3 行)
所以, 当我们在处理下列要求时,会比较麻烦:
有表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 行受影响)
--*/附: 合并与分拆的CLR, sql2005的示例中有:
在安装sql 2005的示例后,默认安装目录为 drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中