SQL Query - Looping-CodePudding

I'm trying to output a record per part for each the quantity in the field. E.g. if a part has a qty of 10 then I'd want that part to be listed 10 times, if the qty was 2 then I'd only want the part to be list twice.

Here's a sample of the data:

Part    Qty
PSR6621581  17
PSR6620952  13
PSR6620754  11
PSR6621436  11
PSR6621029  9
PSR661712   9
PSR661907   9
PSR662998   8
PSR6620574  7
PSR661781   7

Any suggestions?

CodePudding user response：

You can use a recursive CTE to expand the rows. For example:

with
p as (
  select part, qty, 1 as n from t
 union all
  select part, qty, n   1
  from p
  where n < qty
)
select part, qty from p

Result:

 part  qty 
 ----- --- 
 ABC   1   
 DEF   4   
 DEF   4   
 DEF   4   
 DEF   4

See running example at db<>fiddle.

CodePudding user response：

Here is another option. This is using a tally which is the ideal way to handle this type of thing. I keep this view on my system as a crazy fast way of having a tally table.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E 2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E 4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

Now we just create a dummy table with your sample data.

declare @Something table
(
    Part varchar(10)
    , Qty int
)
insert @Something
select 'PSR6621581', 17 union all
select 'PSR6620952', 13 union all
select 'PSR6620754', 11 union all
select 'PSR6621436', 11 union all
select 'PSR6621029', 9 union all
select 'PSR661712', 9 union all
select 'PSR661907', 9 union all
select 'PSR662998', 8 union all
select 'PSR6620574', 7 union all
select 'PSR661781', 7

Now that the setup is complete the query to produce the output you want is super easy and lightning fast to execute.

select s.Part
    , s.Qty
from @Something s
join cteTally t on t.N <= s.Qty
order by s.Part
    , t.N