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.qtycalcOutput:
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)
No comments:
Post a Comment