合并分拆表

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

整理人:中国风(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
*/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值