SQL合并分拆表

-- 合并分拆表
/* *****************************************************************************************************************************************************
合并分拆表数据

整理人：中国风(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 行受影响)
*/


拆分表:

-- > --> (Roy)生成測試數據

if not object_id ( ' Tab ' ) is null
drop table Tab
Go
Create table Tab( [ Col1 ] int , [ COl2 ] nvarchar ( 5 ))
Insert Tab
select 1 ,N ' a,b,c ' union all
select 2 ,N ' d,e ' union all
select 3 ,N ' f '
Go

SQL2000用辅助表:
if object_id ( ' Tempdb..#Num ' ) is not null
drop table #Num
go
select top 100 ID = Identity ( int , 1 , 1 ) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2 = substring (a.Col2,b.ID, charindex ( ' , ' ,a.Col2 + ' , ' ,b.ID) - b.ID)
from
Tab a,#Num b
where
charindex ( ' , ' , ' , ' + a.Col2,b.ID) = b.ID -- 也可用 substring(','+a.COl2,b.ID,1)=','


SQL2005用Xml:

select
a.COl1,b.Col2
from
( select Col1,COl2 = convert (xml, ' <root><v> ' + replace (COl2, ' , ' , ' </v><v> ' ) + ' </v></root> ' ) from Tab)a
outer apply
( select Col2 = C.v.value( ' . ' , ' nvarchar(100) ' ) from a.COl2.nodes( ' /root/v ' )C(v))b




SQL05用CTE:

; with roy as
( select Col1,COl2 = cast ( left (Col2, charindex ( ' , ' ,Col2 + ' , ' ) - 1 ) as nvarchar ( 100 )),Split = cast ( stuff (COl2 + ' , ' , 1 , charindex ( ' , ' ,Col2 + ' , ' ), '' ) as nvarchar ( 100 )) from Tab
union all
select Col1,COl2 = cast ( left (Split, charindex ( ' , ' ,Split) - 1 ) as nvarchar ( 100 )),Split = cast ( stuff (Split, 1 , charindex ( ' , ' ,Split), '' ) as nvarchar ( 100 )) from Roy where split > ''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0 )

生成结果:
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/

转载于:https://www.cnblogs.com/nickelzhang/archive/2011/04/13/2014422.html