根据列将行拆分为2行或更多行

Question

我有一个问题，如果我有一排像这样

|订单号|数量|商品号|
| 123125213 | 3 | fffff111 |

我如何将其分成三行，如下所示：

|订单号|数量|商品号|
| 123125213 | 1 | fffff111 |
| 123125213 | 1 | fffff111 |
| 123125213 | 1 | fffff111 |

/ J

Answer 1

您可以使用递归CTE：

WITH RCTE AS 
(
    SELECT  
        ordernumber, qty, articlenumber, qty AS L
    FROM Table1

    UNION ALL

    SELECT      
        ordernumber, 1, articlenumber, L - 1 AS L
    FROM RCTE
    WHERE L>0
)
SELECT  ordernumber,qty, articlenumber
FROM RCTE WHERE qty = 1

SQLFiddleDEMO

编辑： 基于Marek Grzenkowicz的回答和MatBailie的评论，全新的想法：

WITH CTE_Nums AS 
(
    SELECT MAX(qty) n FROM dbo.Table1
    UNION ALL
    SELECT n-1 FROM CTE_Nums
    WHERE n>1  
)
SELECT  ordernumber ,
        1 AS qty,
        articlenumber
FROM dbo.Table1 t1
INNER JOIN CTE_Nums n ON t1.qty >= n.n

生成从1到max（qty）的数字，并在其上加入表。

SQLFiddle演示