Tuesday, December 3, 2019

SQL SERVER - How to generate more rows from one row (with count limited by source row value)

Suppose this situation, I have this query that returns two rows.
select a.prodid, a.itemid, a.qtycalc from prodtable a
where 
a.prodid in ( '19-047442', '19-047441' ) 
Output:
prodid               itemid                                   qtycalc
-------------------- ---------------------------------------- --------------------
19-047441            5801-989-012_1138-00_lak                 4.0000000000000000
19-047442            5801-989-012_1138-00_lak                 12.0000000000000000

(2 row(s) affected)
Now I want to make "exemplar query" - for every piece in PRODID to generate one row, so total 16 rows.

How to make it ? One variant is here:
with cte as (
select row_number() over ( order by itemid ) AS rownum from inventtable )

select n.rownum, a.prodid, a.qtycalc
from prodtable a
cross join 
 ( select * from cte 
   where 
   rownum < 999 ) n
where 
a.prodid in ( '19-047442', '19-047441' ) and
a.prodpoolid = 'SED_CES_ST' and
a.prodstatus = 4 and
n.rownum <= a.qtycalc
Output:
rownum               prodid               qtycalc
-------------------- -------------------- ---------------------
1                    19-047441            4.0000000000000000
2                    19-047441            4.0000000000000000
3                    19-047441            4.0000000000000000
4                    19-047441            4.0000000000000000
1                    19-047442            12.0000000000000000
2                    19-047442            12.0000000000000000
3                    19-047442            12.0000000000000000
4                    19-047442            12.0000000000000000
5                    19-047442            12.0000000000000000
6                    19-047442            12.0000000000000000
7                    19-047442            12.0000000000000000
8                    19-047442            12.0000000000000000
9                    19-047442            12.0000000000000000
10                   19-047442            12.0000000000000000
11                   19-047442            12.0000000000000000
12                   19-047442            12.0000000000000000

(16 row(s) affected)