how do i get multiple records from 1 record

I have a product table with 15 fields like ItemID (primary),Name ,UPC,Price,Cost, etc.

Now I need to print labels the user can say

• from Item "ABC" I need 15 labels
• from item 'XYZ" I need 10 labels

I need a SQL statement which I will send the ItemID and the label Qty for Each record and it should give me back for ea开发者_JS百科ch label a record for example 15 records for item "ABC" and 10 records for Item "XYZ" and so on

SELECT <fields>
FROM Mytable
Where Item = 'ABC'
GO 10

Will select those fields from that table 10 times in a row in 10 result sets.

Really though it sounds like you need to do what you are trying to do not in SQL, but in your calling application.

I agree this should be done on the client but if you insist, following duplicates each record 100 times and selects the amount you need from it.

;WITH ATable AS (
  SELECT Item = 'ABC'
  UNION ALL SELECT Item = 'XYZ'
)
, Temp (Item, Amount) AS (
  SELECT 'ABC', 15
  UNION ALL SELECT 'XYZ', 10
)
, q AS (
  SELECT  ID = 1
          , Item
  FROM    ATable
  UNION ALL
  SELECT  ID = q.ID +1
          , q.Item
  FROM    q
  WHERE   ID < 100         
)
SELECT  q.*
FROM    q
        INNER JOIN Temp t ON t.Item = q.Item
                             AND t.Amount >= q.ID

You create the dynamic table aliased as r below. Works for amounts up to 2047.

select t.*
from 
 (select label='ABC', required=15 union all
  select label='XYZ', required=10) r
inner join tbl t
    on t.ItemID = r.label
inner join master..spt_values v
    on v.type=Number and v.number between 1 and r.required
order by t.ItemID