CODEWITHSUNDEEP
sql-servert-sql
Julie Nguyen
Julie Nguyen

Reputation: 39

T-SQL Duplicate row based on quantity

I cannot use CTEs in this scenario and was wondering what the best way to create another table that displays an item multiple times (based on it's quantity value).

I.e.

Fruit   Quantity
-----------------
Banana     2
Apple      3

What I want to be produced:

Banana
Banana
Apple
Apple
Apple

I created a table with the possible quantities and this is what I've written as a query so far:

SELECT a.fruit
FROM fruit a
JOIN numbers n ON a.quantity <= n.number

It's not returning the right output for me, do let me know how I can be writing this better.

Again, I am working with T-SQL in a third-party platform where CTEs are not supported.

Upvotes: 0

Views: 291

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your join condition is backwards. Try:

SELECT f.fruit
FROM fruit f JOIN
     numbers n
     ON n.number <= a.quantity;

Upvotes: 2

Related Questions