return multiple rows for a row

Question

i want to return multiple rows based on a qty in a line item.

here is the example,

table = item varchar(10), qty int

records

a  2

b  3

c  2

d  1

run a query to return rows like this

a  2

a  2

b  3

b  3

b  3

c  2

c  2

d  1

2 rows for item a, 3 rows for item b, 2 rows for item c and 1 row for item d)

how i can do this???

---

mark it as answer if it answered your question :)

Answer 1

Perhaps using a "Numbers table"? (Search the net for that phrase to see more examples.)

begin
	with	SourceSet
	as
	(
	select	'a' as Letter
		,2 as Number
	union
	select	'b'
		,3
	union
	select	'c'
		,2
	union
	select	'd'
		,1
	)
	,Numbers
	as
	(
	select	Number
			= row_number()
				over
				(
				order by	[object_id]
				)
		from	sys.objects
	)
	select	SourceSet.*
		from	SourceSet
			cross join	Numbers
		where	(Numbers.Number >= 1)
			and (Numbers.Number <= SourceSet.Number)
end

 

---

ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Answer 2

As Matija noted Numbers table is the solution. Here is example with generating

table with numbers on the fly:

 

WITH

N1 (n) AS (SELECT 1 UNION ALL SELECT 1),

N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),

N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),

N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),

N5 (n) AS (SELECT 1 FROM N4 AS X, N4 AS Y),

Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM N5)

SELECT item, qty

FROM Items

JOIN Nums

  ON n <= qty;

 

---

Plamen Ratchev

Answer 3

In addition to the above sequence generations, you can find more at:

http://www.sqlusa.com/bestpractices2005/sequence/

 

---

Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM