34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #testTable
(
Sn VARCHAR(10) NOT NULL ,
Qty INT NOT NULL
);
GO
INSERT INTO #testTable
VALUES ( 'A', 5 ),
( 'B', 3 ),
( 'C', 2 );
GO
SELECT *
FROM #testTable;
GO
-- 使用辅助的序列表来完成,这里用master..spt_values来替代;
SELECT tt.Sn + '-' + CAST(sv.number AS VARCHAR)
FROM #testTable AS tt
CROSS JOIN master..spt_values AS sv
WHERE sv.number BETWEEN 1 AND tt.Qty
AND sv.type = 'P'
GO
IF EXISTS(SELECT NAME FROM sysobjects AS s WHERE NAME = 'test')
DROP TABLE test
GO
CREATE TABLE test (sn CHAR(1) , qty INT)
GO
INSERT INTO test
SELECT 'A' , 5 UNION ALL
SELECT 'B' , 3 UNION ALL
SELECT 'C' , 2
GO
--執行查詢
SELECT test.sn , a.number + 1 FROM MASTER..spt_values AS a , test WHERE [TYPE] = 'P' AND a.number < test.qty
--結果
/*
sn
---- -----------
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
C 1
C 2
(10 row(s) affected)
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-14 11:29:33
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[T]
if object_id('[T]') is not null drop table [T]
go
create table [T]([SN] nvarchar(2),[QTY] int)
insert [T]
select 'A',5 union all
select 'B',3 union all
select 'C',2
--------------生成数据--------------------------
;WITH cte AS (
select *,SUBSTRING(REPLICATE(sn+',',qty) ,1,LEN(REPLICATE(sn+',',qty))-1)new
from [T] )
select
SUBSTRING(new,number,CHARINDEX(',',new+',',number)-number)+'-'+CAST(ROW_NUMBER()OVER(PARTITION BY new ORDER BY CURRENT_TIMESTAMP) AS VARCHAR(2)) AS New_Col
from
cte a,master..spt_values
where
number >=1 and number<=len(new)
and type='p'
and substring(','+new,number,1)=','
----------------结果----------------------------
/*
New_Col
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A-1
A-2
A-3
A-4
A-5
B-1
B-2
B-3
C-1
C-2
*/
select a.SN +'-'+str(b.QTY ) from (select SN from T ) a inner join (select QTY from T) b on 1=1