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)