CODEWITHSUNDEEP
t-sql
noob
noob

Reputation: 53

t-sql return multiple rows depending on field value

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

Upvotes: 5

Views: 3552

Answers (3)

Lamak
Lamak

Reputation: 70638

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.

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453233

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.

Upvotes: 4

DVK
DVK

Reputation: 129403

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

Upvotes: 1

Related Questions