-- Title: 在SQL中分类合并数据行 -- Author: dobear Mail(MSN): [email protected] -- Environment: Vista + SQL2005 -- Date: 2008-04-22 -- =============================================================================--1. 创建表,添加测试数据 CREATE TABLE tb(id int, [value] varchar(10)) INSERT tb 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 tb /**//* id value ----------- ---------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc(5 row(s) affected) */ --2 在SQL2000只能用自定义函数实现 ----2.1 创建合并函数fn_strSum,根据id合并value值 GO CREATE FUNCTION dbo.fn_strSum(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @values varchar(8000) SET @values = '' SELECT @values = @values + ',' + value FROM tb WHERE id=@id RETURN STUFF(@values, 1, 1, '') END GO-- 调用函数 SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id DROP FUNCTION dbo.fn_strSum----2.2 创建合并函数fn_strSum2,根据id合并value值 GO CREATE FUNCTION dbo.fn_strSum2(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @values varchar(8000) SELECT @values = isnull(@values + ',', '') + value FROM tb WHERE id=@id RETURN @values END GO-- 调用函数 SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id DROP FUNCTION dbo.fn_strSum2 --3 在SQL2005中的新解法 ----3.1 使用OUTER APPLY SELECT * FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM tb N WHERE id = A.id FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, '') )N----3.2 使用XML SELECT id, [values]=STUFF((SELECT ','+[value] FROM tb t WHERE id=tb.id FOR XML PATH('')), 1, 1, '') FROM tb GROUP BY id--4 删除测试表tb drop table tb/**//* id values ----------- -------------------- 1 aa,bb 2 aaa,bbb,ccc(2 row(s) affected) 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/dobear_0922/archive/2008/04/22/2313839.aspx
/****************************************************************************************************************************************************** 合并分拆表数据 整理人:中国风(Roy) 日期:2008.06.06 ******************************************************************************************************************************************************/ --> --> (Roy)生成測試數據 if not object_id('Tab') is null drop table Tab Go Create table Tab([Col1] int,[Col2] nvarchar(1)) Insert Tab select 1,N'a' union all select 1,N'b' union all select 1,N'c' union all select 2,N'd' union all select 2,N'e' union all select 3,N'f' Go 合并表: SQL2000用函数: go if object_id('F_Str') is not null drop function F_Str go create function F_Str(@Col1 int) returns nvarchar(100) as begin declare @S nvarchar(100) select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1 return @S end go Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go SQL2005用XML: 方法1: select a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') from (select distinct COl1 from Tab) a Cross apply (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 方法2: select a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) from (select distinct COl1 from Tab) a cross apply (select Col2=(select COl2 from Tab where COl1=a.COl1 FOR XML AUTO, TYPE) .query(' <Tab> {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")} {concat("",string(/Tab[last()]/@COl2))} </Tab>') )b SQL05用CTE: ;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) ,Roy2 as (select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 union all select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 生成结果: /* Col1 COl2 ----------- ------------ 1 a,b,c 2 d,e 3 f (3 行受影响) */
/* 标题:按某字段合并字符串之一(简单合并) 作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 时间:2008-11-06 地点:广东深圳描述:将如下形式的数据按id字段合并value字段。 id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果: id value ------ ----------- 1 aa,bb 2 aaa,bbb,ccc 即:group by id, 求 value 的和(字符串相加) */ --1、sql2000中只能用自定义的函数解决 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') gocreate function dbo.f_str(@id varchar(10)) returns varchar(1000) as begin declare @str varchar(1000) select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id return @str end go--调用函数 select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str drop table tb --2、sql2005中的方法 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') goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '') from tb group by iddrop table tb --3、使用游标合并数据 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 declare @t table(id int,value varchar(100))--定义结果集表变量 --定义游标并进行合并处理 declare my_cursor cursor local for select id , value from tb declare @id_old int , @id int , @value varchar(10) , @s varchar(100) open my_cursor fetch my_cursor into @id , @value select @id_old = @id , @s='' while @@FETCH_STATUS = 0 begin if @id = @id_old select @s = @s + ',' + cast(@value as varchar) else begin insert @t values(@id_old , stuff(@s,1,1,'')) select @s = ',' + cast(@value as varchar) , @id_old = @id end fetch my_cursor into @id , @value END insert @t values(@id_old , stuff(@s,1,1,'')) close my_cursor deallocate my_cursorselect * from @t drop table tb
非常感谢大家。。按 #1 楼以下方法输出内容,很多乱码(空格,回车,通通失效了),如何处理? --3 在SQL2005中的新解法 ----3.1 使用OUTER APPLY SELECT * FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM tb N WHERE id = A.id FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, '') )N
-- Author: dobear Mail(MSN): [email protected]
-- Environment: Vista + SQL2005
-- Date: 2008-04-22
-- =============================================================================--1. 创建表,添加测试数据
CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb 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 tb
/**//*
id value
----------- ----------
1 aa
1 bb
2 aaa
2 bbb
2 ccc(5 row(s) affected)
*/
--2 在SQL2000只能用自定义函数实现
----2.1 创建合并函数fn_strSum,根据id合并value值
GO
CREATE FUNCTION dbo.fn_strSum(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SET @values = ''
SELECT @values = @values + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@values, 1, 1, '')
END
GO-- 调用函数
SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id
DROP FUNCTION dbo.fn_strSum----2.2 创建合并函数fn_strSum2,根据id合并value值
GO
CREATE FUNCTION dbo.fn_strSum2(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SELECT @values = isnull(@values + ',', '') + value FROM tb WHERE id=@id
RETURN @values
END
GO-- 调用函数
SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id
DROP FUNCTION dbo.fn_strSum2
--3 在SQL2005中的新解法
----3.1 使用OUTER APPLY
SELECT *
FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N----3.2 使用XML
SELECT id, [values]=STUFF((SELECT ','+[value] FROM tb t WHERE id=tb.id FOR XML PATH('')), 1, 1, '')
FROM tb
GROUP BY id--4 删除测试表tb
drop table tb/**//*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc(2 row(s) affected)
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/dobear_0922/archive/2008/04/22/2313839.aspx
合并分拆表数据 整理人:中国风(Roy) 日期:2008.06.06
******************************************************************************************************************************************************/ --> --> (Roy)生成測試數據 if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go 合并表: SQL2000用函数: go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go SQL2005用XML: 方法1: select
a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
from
(select distinct COl1 from Tab) a
Cross apply
(select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 方法2: select
a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from
(select distinct COl1 from Tab) a
cross apply
(select Col2=(select COl2 from Tab where COl1=a.COl1 FOR XML AUTO, TYPE)
.query(' <Tab>
{for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")}
{concat("",string(/Tab[last()]/@COl2))}
</Tab>')
)b SQL05用CTE: ;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)
,Roy2 as
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1
union all
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- ------------
1 a,b,c
2 d,e
3 f (3 行受影响)
*/
标题:按某字段合并字符串之一(简单合并)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-06
地点:广东深圳描述:将如下形式的数据按id字段合并value字段。
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id value
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)
*/
--1、sql2000中只能用自定义的函数解决
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')
gocreate function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
--2、sql2005中的方法
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')
goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by iddrop table tb
--3、使用游标合并数据
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
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
if @id = @id_old
select @s = @s + ',' + cast(@value as varchar)
else
begin
insert @t values(@id_old , stuff(@s,1,1,''))
select @s = ',' + cast(@value as varchar) , @id_old = @id
end
fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursorselect * from @t
drop table tb
--3 在SQL2005中的新解法
----3.1 使用OUTER APPLY
SELECT *
FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N