Answered by:
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 :)Saturday, February 5, 2011 12:03 AM
Answers

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 Proposed as answer by Chintak Chhapia Saturday, February 5, 2011 12:19 AM
 Marked as answer by Kalman Toth Thursday, February 10, 2011 9:26 AM
Saturday, February 5, 2011 12:11 AM
All replies

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 Proposed as answer by Chintak Chhapia Saturday, February 5, 2011 12:19 AM
 Marked as answer by Kalman Toth Thursday, February 10, 2011 9:26 AM
Saturday, February 5, 2011 12:11 AM 
As Matija noted Numbers table is the solution. Here is example with generatingtable with numbers on the fly:WITHN1 (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, qtyFROM ItemsJOIN NumsON n <= qty;
Plamen RatchevSaturday, February 5, 2011 2:10 AM 
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 SLAMThursday, February 10, 2011 9:31 AM