T-sql returns several rows depending on the value of the field

Question

I am trying to start export on a system that only allows t-sql. I know enough php to make a foreach loop, but I don't know enough t-sql to generate multiple rows for a given amount. I need a result to make a list of items with "1 of 4", like the data included in the result

given type table

orderid, product, quantity 1000,ball,3 1001,bike,4 1002,hat,2 

how to get the result of selecting a query, for example:

orderid, item_num, total_items, Product

1000,1,3 ball

1000,2,3 ball

1000,3,3 ball

1001,1,4, bike

1001,2,4, bike

1001,3,4, bike

1001,4,4, bike

1002,1,2 hat

1002,2,2 hat

Answer 1

You can do this using the helper table.

 ;WITH T(orderid, product, quantity) AS ( select 1000,'ball',3 union all select 1001,'bike',4 union all select 1002,'hat',2 ) SELECT orderid, number as item_num, quantity as total_items, product FROM T JOIN master..spt_values on number> 0 and number <= quantity where type='P' 

Note. The code above uses the master..spt_values table - this is for demo purposes only. I suggest you create your own table of tables using one of the methods here .

Answer 2

If you are using SQL Server 2005 or later, you can try a recursive CTE instead of a count table.

 ;WITH CTE AS ( SELECT orderid, 1 item_num, product, quantity FROM YourTable UNION ALL SELECT orderid, item_num+1, product, quantity FROM CTE WHERE item_num < quantity ) SELECT * FROM CTE OPTION (MAXRECURSION 0) 

I am not on a computer with a database engine where I can verify this, so let me know how this happens.

Answer 3

Well, if you know the maximum value for # products for any product (and it is not too large, say 4), you can:

• Create a helper table called Nums containing 1 integer column n, with rows containing 1,2,3,4

• Run

    SELECT * from Your_table, Nums WHERE Nums.n <= Your_table.quantity