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

Question

我有个问题
如果我有一排看起来像这样

|订单编号|数量|商品编号|
| 123125213| 3 |ffff111 |

我怎样才能把它分成这样的三行:

|订单编号|数量|商品编号|
| 123125213| 1 |ffff111 |
| 123125213| 1 |ffff111 |
| 123125213| 1 |ffff111 |

/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 DEMO