t-sql return multiple rows depending on field value

Question

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

given a table like

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

how do i get a select query result like:

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

score 4 · Accepted Answer · edited May 23 '17 at 10:24

4

You can do this with the aid of an auxiliary numbers 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'

NB: The code above uses the master..spt_values table - this is just for demo purposes I suggest you create your own tally table using one of the techniques here.

edited May 23 '17 at 10:24

Community

1
1

answered Feb 15 '11 at 23:01

Martin Smith

438,706
87
741
845

@Thomas - Yep. Every auxiliary numbers table will have some limit. @noob - This is just for demo purposes I suggest you create your own tally table using one of the techniques here http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232 – Martin Smith Feb 15 '11 at 23:21
+1, but why not `number between 1 and quantity`? I mean, I'd like to know if that makes any difference. – Andriy M Feb 16 '11 at 00:04
@Andriy - I did already think about making that edit. I agree it's neater but hopefully the OP will be using a permanent tally table from `1..n` so they can completely drop the `number> 0` check in their actual query. – Martin Smith Feb 16 '11 at 00:08
1

thank you everyone. I just learned a little bit more SQL and this solution solved my problem exactly. – noob Feb 16 '11 at 15:22

score 3 · Answer 2 · edited Jun 18 '13 at 08:48

If you are on SQL Server 2005 or later version, then you can try a recursive CTE instead of a tally 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'm not on a computer with a database engine where I can test this, so let me know how it goes.

score 1 · Answer 3 · answered Feb 15 '11 at 23:02

1

Well, IF you know the maximum value for the # of products for any product (and it's not too big, 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

answered Feb 15 '11 at 23:02

DVK

126,886
32
213
327

CROSS JOIN syntax would be nicer – ThomasMcLeod Feb 15 '11 at 23:12
not a biggy. Replace `,` operator in table list with `CROSS JOIN` operator. Both generate cartesian product. – ThomasMcLeod Feb 16 '11 at 00:24
@Thomas is there a difference? – DVK Feb 16 '11 at 03:27

t-sql return multiple rows depending on field value

3 Answers3