SQL 根据列值重复行 N 次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33327837/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Repeat Rows N Times According to Column Value
提问by franchesco totti
I have following table.
我有下表。
Table A:
ID ProductFK Quantity Price
------------------------------------------------
10 1 2 100
11 2 3 150
12 1 1 120
----------------------------------------------
I need select that repeat Rows N Time According to Quantity Column Value.
我需要根据数量列值选择重复行 N 次。
So I need following select result:
所以我需要以下选择结果：
ID ProductFK Quantity Price
------------------------------------------------
10 1 1 100
10 1 1 100
11 2 1 150
11 2 1 150
11 2 1 150
12 1 1 120
采纳答案by Amit
You could do that with a recursive CTE using UNION ALL:
您可以使用递归 CTE 来做到这一点UNION ALL：
;WITH cte AS
(
SELECT * FROM Table1
UNION ALL
SELECT cte.[ID], cte.ProductFK, (cte.[Order] - 1) [Order], cte.Price
FROM cte INNER JOIN Table1 t
ON cte.[ID] = t.[ID]
WHERE cte.[Order] > 1
)
SELECT [ID], ProductFK, 1 [Order], Price
FROM cte
ORDER BY 1
Here's a working SQLFiddle.
这是一个有效的SQLFiddle。
Here's a longer explanation of this technique.
Since your input is too large for this recursion, you could use an auxillary table to have "many" dummy rows and then use SELECT TOP([Order])for each input row (CROSS APPLY):
由于您的输入对于此递归而言太大，您可以使用辅助表来拥有“许多”虚拟行，然后SELECT TOP([Order])用于每个输入行 ( CROSS APPLY)：
;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b)
SELECT t.[ID], t.ProductFK, 1 [Order], t.Price
FROM Table1 t CROSS APPLY (
SELECT TOP(t.[Order]) N
FROM E16) ca
ORDER BY 1
(The auxillary table is borrowed from here, it allows up to 65536 rows per input row and can be extended if required)
（辅助表是从这里借来的，它允许每个输入行最多 65536 行，如果需要可以扩展）
Here's a working SQLFiddle.
这是一个有效的SQLFiddle。
回答by Siyavash Hamdi
You can use a simple JOINto get the desired result as below:
您可以使用简单的方法JOIN来获得所需的结果，如下所示：
SELECT t1.*, t2.number + 1 RepeatNumber
FROM TableA t1
JOIN master.dbo.spt_values t2 ON t2.type = 'P' AND t2.number < t1.Quantity
The above query repeats each record by the specified number in Quantitycolumn.
上面的查询按Quantity列中指定的数量重复每条记录。
Note for master.dbo.spt_values on type = 'P':
This table is used for getting a series of number which is hard-coded in it by
condition of type = 'P'.
注意master.dbo.spt_values on type = 'P'：
此表用于获取由 的
条件硬编码在其中的一系列数字type = 'P'。
回答by Ashish Srivastava
CREATE TAblE #temp
(
T_Name VARCHAR(50),
T_Times BIGINT
)
INSERT INTO #temp(T_Name,T_Times) VALUES ('ASHISH',4)
INSERT INTO #temp(T_Name,T_Times) VALUES ('PANKAJ',3)
INSERT INTO #temp(T_Name,T_Times) VALUES ('RUPESH',2)
INSERT INTO #temp(T_Name,T_Times) VALUES ('MANISH',5)
SELECT t.T_Name ,t.T_Times FROM
(SELECT T_Name,T_Times,CAST(('<val>'+REPLICATE(T_Name+'</val><val>',T_Times-1)
+'</val>') AS XML )AS X FROM #temp)t CROSS APPLY t.X.nodes('/val')y(z)
drop table #temp
