SQL return multiple rows from one record

Question

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 working 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

Answer 1

you can do it using dynamic sql. What flavor of sql are you using?

Answer 2

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.

Answer 3

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.

Answer 4

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.