-- ============================================================================= -- 標題: 合并列值 -- 整理: takako_mu -- 时间: 2009-10-19 -- 地点: 昆山 -- ============================================================================= /* 表結構如下: id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc 結果如下: id values ------ ----------- 1 aa,bb 2 aaa,bbb,ccc */create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go ------------------------------------------------------------ --1. sql2000舊方法:利用函數 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, value = dbo.f_str(id) from tb group by id
------------------------------------------------------------ --2. sql2000舊方法:另一個函數 create function f_hb(@id int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(value as varchar) from tb where id = @id set @str = right(@str , len(@str) - 1) return(@str) end go select distinct id ,dbo.f_hb(id) as value from tb ------------------------------------------------------------ --3. sql2005:outer apply select A.id,B.[newValues] from (select distinct id from tb)A outer apply ( select [newValues]= stuff(replace(replace( ( select [value] from tb N where id = A.id for xml auto ), '<N value="', ','), '"/>', ''), 1, 1, '') )B/* 注: APPLY 是在一个查询的 FROM 子句中指定的新的关系运算符。 它允许您对外部表的每一行调用表值函数,可选地使用外部表的列作为函数的参数。 APPLY 运算符有两种形式:CROSS APPLY 和 OUTER APPLY。 如果表值函数为其返回一个空集合的话,前者不返回外部表的行,而后者则返回一个 NULL 值的行而不是函数的列。*/------------------------------------------------------------ --4. sql2005:for xml path select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id
insert into test values(1,1,'aaa') insert into test values(2,2,'bbb') insert into test values(2,2,'ccc') insert into test values(3,3,'ddd') insert into test values(4,4,'fff') GO
-- sql 2005 SELECT * FROM( SELECT DISTINCT id, iid FROM test )A OUTER APPLY( SELECT name = STUFF(REPLACE(REPLACE( ( SELECT v = name FROM test t WHERE id = A.id AND iid = A.id FOR XML AUTO ), N'<t v="', N'/'), N'"/>', N''), 1, 1, N'') )B GO
DROP TABLE test
-- 结果 id iid name ----------- ----------- ------------ 1 1 aaa 2 2 bbb/ccc 3 3 ddd 4 4 fff
(4 行受影响) sqlserver200: create table test(id int,txt varchar(10)) insert test select 1,'aaa' union all select 1,'bbb' union all select 2,'ccc' union all select 3,'ddd' union all select 3,'eee' union all select 3,'fff' --select * from test go
create function Gettxt(@id int) returns varchar(8000) as begin declare @s varchar(8000) set @s='' select @s=@s +';' +txt from test where id=@id --return @s return stuff(@s,1,1,'') end go
select id,dbo.Gettxt(id) txt from test group by id go
-- =============================================================================
-- 標題: 合并列值
-- 整理: takako_mu
-- 时间: 2009-10-19
-- 地点: 昆山
-- =============================================================================
/*
表結構如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc 結果如下:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
*/create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go ------------------------------------------------------------
--1. sql2000舊方法:利用函數
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, value = dbo.f_str(id) from tb group by id
------------------------------------------------------------
--2. sql2000舊方法:另一個函數
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
end
go
select distinct id ,dbo.f_hb(id) as value from tb ------------------------------------------------------------
--3. sql2005:outer apply
select A.id,B.[newValues] from
(select distinct id from tb)A
outer apply
(
select [newValues]= stuff(replace(replace(
(
select [value] from tb N
where id = A.id
for xml auto
), '<N value="', ','), '"/>', ''), 1, 1, '')
)B/*
注: APPLY 是在一个查询的 FROM 子句中指定的新的关系运算符。
它允许您对外部表的每一行调用表值函数,可选地使用外部表的列作为函数的参数。
APPLY 运算符有两种形式:CROSS APPLY 和 OUTER APPLY。
如果表值函数为其返回一个空集合的话,前者不返回外部表的行,而后者则返回一个 NULL 值的行而不是函数的列。*/------------------------------------------------------------
--4. sql2005:for xml path
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
在SQL Server 2005 中,系统提供了一种方法。
如:
--2005系统提供了功能聚合
create table test(id int,iid int,name varchar(40))
insert into test values(1,1,'aaa')
insert into test values(2,2,'bbb')
insert into test values(2,2,'ccc')
insert into test values(3,3,'ddd')
insert into test values(4,4,'fff')
GO
-- sql 2005
SELECT *
FROM(
SELECT DISTINCT
id, iid
FROM test
)A
OUTER APPLY(
SELECT name = STUFF(REPLACE(REPLACE(
(
SELECT v = name
FROM test t
WHERE id = A.id
AND iid = A.id
FOR XML AUTO
), N'<t v="', N'/'), N'"/>', N''), 1, 1, N'')
)B
GO
DROP TABLE test
-- 结果
id iid name
----------- ----------- ------------
1 1 aaa
2 2 bbb/ccc
3 3 ddd
4 4 fff
(4 行受影响) sqlserver200:
create table test(id int,txt varchar(10))
insert test
select 1,'aaa' union all
select 1,'bbb' union all
select 2,'ccc' union all
select 3,'ddd' union all
select 3,'eee' union all
select 3,'fff'
--select * from test
go
create function Gettxt(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s +';' +txt from test where id=@id
--return @s
return stuff(@s,1,1,'')
end
go
select id,dbo.Gettxt(id) txt from test group by id
go
drop function Gettxt
drop table test