Split a row into 2 or more rows depending on the column

Question

I have a question If I have one line that looks like this:

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

How can I split this into three lines as follows:

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

/ J

Answer 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 Grzhenkovich’s answer and MatBailie's comment, all 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 >= nn 

Creating a number from 1 to max (qty) and joining a table on it.

SQLFiddle DEMO

Answer 2

Here's a quick hack using an extra table populated with a row of rows suitable for the qty values ​​you expect:

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

Answer 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 

Answer 4

You can also use the option with the 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, it is preferable to use your own sequence table.

See SQLFiddle