请教如何实现记录拆分,效果如下:
拆分前记录
Item ------TypeList-----Value
001 A,B 10
002 A,B,C,D 20拆分后记录
Item ------TypeList-----Value
001 A 10
001 B 10
002 A 5
002 B 5
002 C 5
002 D 5
备注:Value按TypeList中Type个数平均谢谢!
拆分前记录
Item ------TypeList-----Value
001 A,B 10
002 A,B,C,D 20拆分后记录
Item ------TypeList-----Value
001 A 10
001 B 10
002 A 5
002 B 5
002 C 5
002 D 5
备注:Value按TypeList中Type个数平均谢谢!
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([Item] varchar(3),[TypeList] varchar(7),[Value] int)
insert [TB]
select '001','A,B',10 union all
select '002','A,B,C,D',20
GO
--1.2000/2005通用方法
SELECT
a.[Item],
[TypeList]=SUBSTRING(a.[TypeList],number,CHARINDEX(',',a.[TypeList]+',',number)-b.number),[Value]/(LEN(REPLACE(a.TypeList,',',',,'))-LEN(a.TypeList)+1)
FROM [TB] a
JOIN master..spt_values b
ON b.type='P'
--AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可
AND CHARINDEX(',',','+a.[TypeList],number)=number
--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
insert into tb select '001','A,B',10
insert into tb select '002','A,B,C,D',20
go
;with cte as(
select item,left(typelist,charindex(',',typelist)-1)as typelist,right(typelist,len(typelist)-charindex(',',typelist))as lastlist,v from tb where charindex(',',typelist)>0
union all
select item,typelist,null,v from tb where charindex(',',typelist)=0
union all
select item,lastlist,null,v from cte where charindex(',',lastlist)=0
union all
select item,left(lastlist,charindex(',',lastlist)-1),right(lastlist,len(lastlist)-charindex(',',lastlist)),v from cte where charindex(',',lastlist)>0
)
select a.item,a.typelist,b.v from cte a inner join (
select item,convert(decimal(6,2),v)/count(*) as v from cte group by item,v
)b on a.item=b.item
order by item
go
drop table tb
/*
item typelist v
---------- ---------- ---------------------------------------
001 A 5.0000000000000
001 B 5.0000000000000
002 A 5.0000000000000
002 B 5.0000000000000
002 C 5.0000000000000
002 D 5.0000000000000(6 行受影响)
*/