【】一行记录根据某字段拆分多行！

Question

InPut：
Id    ProductName       SumCout
1       iPad2             2     
2      iPhone5            3OutPut：
Id   ProductName         Count
1       iPad2             1
1       iPad2             1
2       iPhone5           1
2       iPhone5           1
2       iPhone5           1Ps:怎么解决，Help!

Answer 1

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([Id] int,[ProductName] varchar(7),[SumCout] int)
insert [TB]
select 1,'iPad2',2 union all
select 2,'iPhone5',3select t.[Id],t.[ProductName],[Count]=1 from [TB] t INNER JOIN [master].dbo.spt_values sv on t.SumCout>sv.number
WHERE sv.[type]='p'/*
Id          ProductName Count
----------- ----------- -----------
1           iPad2       1
1           iPad2       1
2           iPhone5     1
2           iPhone5     1
2           iPhone5     1(5 行受影响)
*/
drop table [TB]

Answer 2

with tb(id,name,sumcount)
 as(
 select 1,'iPad2',2 union all
 select 2,'iPhone5',3)
 select id,tb.name,1 sumcount from tb,master..spt_values where number between 1 and sumcount and type='p'

Answer 3

http://bbs.csdn.net/topics/390292923参照 这个