根据某个字段的值把一行拆成多行

[Teaerror]

原始数据例

项目 次数
跳绳 3
俯卧撑 4
蛙跳 2

变成
项目
跳绳1
跳绳2
跳绳3
俯卧撑1
俯卧撑2
俯卧撑3
俯卧撑4
蛙跳1
蛙跳2

[發糞塗牆]

[code=sql]----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-04-15 16:25:27
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
--	Jun 17 2011 00:54:03 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([项目] varchar(6),[次数] int)
insert [huang]
select '跳绳',3 union all
select '俯卧撑',4 union all
select '蛙跳',2
--------------开始查询--------------------------
;WITH cte AS (
select * ,REPLICATE([项目]+',',[次数]) [项目2]
from [huang])

SELECT  
        SUBSTRING([项目2], number, CHARINDEX(',', [项目2] + ',', number) - number)+CONVERT(VARCHAR(10),ROW_NUMBER()OVER(PARTITION BY SUBSTRING([项目2], number, CHARINDEX(',', [项目2] + ',', number) - number) ORDER BY GETDATE())) AS [项目2]
FROM    cte a ,
        master..spt_values
WHERE   number >= 1
        AND number < LEN([项目2])
        AND type = 'p'
        AND SUBSTRING(',' + [项目2], number, 1) = ','
----------------结果----------------------------
/* 
项目2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
俯卧撑1
俯卧撑2
俯卧撑3
俯卧撑4
跳绳1
跳绳2
跳绳3
蛙跳1
蛙跳2
*/

[/code]

[rockyshi123]

DECLARE @Items TABLE ( ItemName NVARCHAR(100) , Times INT ) INSERT INTO @Items ( ItemName , Times ) SELECT N'跳绳' , 3 UNION ALL SELECT N'俯卧撑' , 4 UNION ALL SELECT N'蛙跳' , 2 SELECT A.ItemName + CAST(C.RowNumber AS NVARCHAR) FROM @Items A INNER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY NAME ) AS RowNumber FROM sys.columns C ) C ON C.RowNumber <= A.Times

[唐诗三百首]

create table tea(项目 varchar(10), 次数 int)

insert into tea
select '跳绳', 3 union all
select '俯卧撑', 4 union all
select '蛙跳', 2


select a.项目+rtrim(b.number) '项目'
 from tea a
 cross join
 (select number
  from master.dbo.spt_values 
  where type='P') b
where b.number>0 and b.number<=a.次数

/*
项目
----------------------
跳绳1
跳绳2
跳绳3
俯卧撑1
俯卧撑2
俯卧撑3
俯卧撑4
蛙跳1
蛙跳2

(9 row(s) affected)
*/