根据列将一行拆分为 2 行或更多行 [英] Split a row on 2 or more rows depending on a column

问题描述

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

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

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

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

/J

解决方案

您可以使用递归 CTE:

WITH RCTE AS(选择订单号，数量，货号，数量 AS L从表 1联合所有选择订单号，1，货号，L - 1 AS L来自 RCTEL>0的地方)选择订单号、数量、货号来自 RCTE，其中数量 = 1

SQLFiddleDEMO

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

WITH CTE_Nums AS(SELECT MAX(qty) n FROM dbo.Table1联合所有SELECT n-1 FROM CTE_Nums其中 n>1)选择订单号，1 AS 数量，文章编号从 dbo.Table1 t1INNER JOIN CTE_Nums n ON t1.qty >= n.n

生成从 1 到 max(qty) 的数字并在其上连接表.

SQLFiddle 演示

I have a question If I have one row that looks like this

|ordernumber|qty|articlenumber|
|  123125213| 3 |fffff111     |

How can I split this into three rows like this:

|ordernumber|qty|articlenumber|
|  123125213| 1 |fffff111     |
|  123125213| 1 |fffff111     |
|  123125213| 1 |fffff111     |

/J

解决方案

You can use recursive 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

EDIT: Based on Marek Grzenkowicz's answer and MatBailie's comment, whole new idea:

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

Generating number from 1 to max(qty) and join table on it.

SQLFiddle DEMO

这篇关于根据列将一行拆分为 2 行或更多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！