SQL return multiple rows from one record

This is the opposite of reducing repeating records. SQL query to create physical inventory checklists If widget-xyz has a qty of 1 item return 1 row, but if it has 5, return 5 rows etc. For all widgets in a particular warehouse.

Previously this was handled with a macro workin开发者_运维百科g through a range in excel, checking the qty column. Is there a way to make a single query instead?

The tables are FoxPro dbf files generated by an application and I am outputting this into html

Instead of generating an xml string and using xml parsing functions to generate a counter as Nestor has suggested, you might consider joining on a recursive CTE as a counter, as LukLed has hinted to:

WITH Counter AS
(
    SELECT 0 i

    UNION ALL

    SELECT i + 1
    FROM Counter
    WHERE i < 100
),
Data AS
(
    SELECT 'A' sku, 1 qty
    UNION
    SELECT 'B', 2
    UNION
    SELECT 'C', 3
)
SELECT * 
FROM Data
    INNER JOIN Counter ON i < qty

According to query analyzer, this query is much faster than the xml pseudo-table. This approach also gives you a recordset with a natural key (sku, i).

There is a default recursion limit of 100 in MSSQL that will restrict your counter. If you have quantities > 100, you can either increase this limit, use nested counters, or create a physical table for counting.

For SQL 2005/2008, take a look at

CROSS APPLY

What I would do is CROSS APPLY each row with a sub table with as many rows as qty has. A secondary question is how to create that sub table (I'd suggest to create an xml string and then parse it with the xml operators) I hope this gives you a starting pointer....

Starting with

declare @table table (sku int, qty int);
insert into @table values (1, 5), (2,4), (3,2);
select * from @table;

sku         qty
----------- -----------
1           5
2           4
3           2

You can generate:

with MainT as (
    select *, convert(xml,'<table>'+REPLICATE('<r></r>',qty)+'</table>') as pseudo_table
    from @table 
)
select p.sku, p.qty
from MainT p 
CROSS APPLY 
(
    select p.sku from p.pseudo_table.nodes('/table/r') T(row)
) crossT


sku         qty
----------- -----------
1           5
1           5
1           5
1           5
1           5
2           4
2           4
2           4
2           4
3           2
3           2

Is that what you want? Seriously dude... next time put more effort writing your question. It's impossible to know exactly what you are looking for.

You can use table with number from 1 to max(quantity) and join your table by quantity <= number. You can do it in many ways, but it depends on sql engine.

You can do this using dynamic sql.