Split a row on 2 or more rows depending on a colum

问题:

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

回答1:

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:

Here's a quick hack using an additional table populated with a number of rows suitable for the qty values you are expecting:

-- helper table
CREATE TABLE qty_splitter (qty int)

INSERT INTO qty_splitter VALUES (1)
INSERT INTO qty_splitter VALUES (2)
INSERT INTO qty_splitter VALUES (3)
INSERT INTO qty_splitter VALUES (4)
INSERT INTO qty_splitter VALUES (5)
....

-- query to produce split rows
SELECT t1.ordernumber, 1, t1.articlenumber
FROM table1 t1
INNER JOIN qty_splitter qs on t.qty >= qs.qty

回答3:

You can do it using CTE

declare @t table (ordername varchar(50), qty int)

    insert into @t values ('ord1',5),('ord2',3)

    ;with cte as
    (
        select ordername, qty, qty-1 n
        from @t
        union all
        select ordername, qty, n-1
        from cte
        where n>0
    )
    select ordername,1
    from cte
    order by ordername

回答4:

Also you can use option with master..spt_values system table.

SELECT t.ordernumber, o.qty, t.articlenumber
FROM dbo.SplitTable t CROSS APPLY (
                                   SELECT 1 AS qty
                                   FROM master..spt_values v
                                   WHERE v.TYPE = 'P' AND v.number < t.qty
                                   ) o

However, for this purpose is preferable to use its own sequence table

See demo on SQLFiddle