数据库辅导：SQL中合并分拆表方法汇总

整理的方法如下：
　　Title: 在SQL中分类合并数据行
　　Author: dobear Mail(MSN): dobear_0922@hotmail.com
　　Environment: Vista + SQL2005
　　Date: 20080422
　　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
　　), '', ''), 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)
　　/
　　roy_88 兄弟整理的方法：
　　合并分拆表
　　/
　　合并分拆表数据
　　整理人：中国风(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('
　　{for $i in /Tab[position()