SQL how to split a quantity into several rows based on the quantity on the target source

Question

I have two tables where I want to join and show the quantity with details. table combined with ITM, DIA and total Qty in both tables in combination ITM / DIA

I want to break the number of tables2 into table1 and populate the data of table2 along with the data of the table.

I have below the data for your reference, "table1" and "table2". and you can see the expected result in the table "tableResult"

CREATE TABLE table1 (`ITM` varchar(5), `DIA` varchar(4), `LOC` varchar(4), `ID` varchar(3), `QTY` int) ; INSERT INTO table1 (`ITM`, `DIA`, `LOC`, `ID`, `QTY`) VALUES ('Item1', 'DIA1', 'LOC1', 'ID1', 3), ('Item1', 'DIA1', 'LOC2', 'ID2', 4), ('Item1', 'DIA1', 'LOC2', 'ID2', 6), ('Item1', 'DIA2', 'LOC2', 'ID2', 6), ('Item1', 'DIA2', 'LOC3', 'ID3', 18), ('Item1', 'DIA2', 'LOC4', 'ID4', 90), ('Item1', 'DIA2', 'LOC4', 'ID5', 23), ('Item1', 'DIA3', 'LOC5', 'ID6', 50), ('Item1', 'DIA3', 'LOC6', 'ID7', 20), ('Item2', 'DIA1', 'LOC4', 'ID8', 44), ('Item2', 'DIA2', 'LOC5', 'ID8', 21), ('Item2', 'DIA3', 'LOC6', 'ID9', 20) ; CREATE TABLE table2 (`ITM` varchar(5), `DIA` varchar(4), `NTA` varchar(5), `QTY` int) ; INSERT INTO table2 (`ITM`, `DIA`, `NTA`, `QTY`) VALUES ('Item1', 'DIA1', 'NTA1', 10), ('Item1', 'DIA1', 'NTA2', 3), ('Item1', 'DIA2', 'NTA3', 30), ('Item1', 'DIA2', 'NTA4', 7), ('Item1', 'DIA2', 'NTA5', 100), ('Item1', 'DIA3', 'NTA6', 70), ('Item2', 'DIA1', 'NTA7', 22), ('Item2', 'DIA1', 'NTA8', 20), ('Item2', 'DIA2', 'NTA9', 6), ('Item2', 'DIA2', 'NTA10', 15), ('Item2', 'DIA3', 'NTA11', 8), ('Item2', 'DIA3', 'NTA11', 12) ; CREATE TABLE tableResult (`ITM` varchar(5), `DIA` varchar(4), `LOC` varchar(4), `ID` varchar(3), `QTY` int, `NTA` varchar(5), `NewQTY` int) ; INSERT INTO tableResult (`ITM`, `DIA`, `LOC`, `ID`, `QTY`, `NTA`, `NewQTY`) VALUES ('Item1', 'DIA1', 'LOC1', 'ID1', 3, 'NTA1', 3), ('Item1', 'DIA1', 'LOC2', 'ID2', 4, 'NTA1', 4), ('Item1', 'DIA1', 'LOC2', 'ID2', 6, 'NTA1', 3), ('Item1', 'DIA1', 'LOC2', 'ID2', 6, 'NTA2', 3), ('Item1', 'DIA2', 'LOC2', 'ID2', 6, 'NTA3', 6), ('Item1', 'DIA2', 'LOC3', 'ID3', 18, 'NTA3', 18), ('Item1', 'DIA2', 'LOC4', 'ID4', 90, 'NTA3', 6), ('Item1', 'DIA2', 'LOC4', 'ID4', 90, 'NTA4', 7), ('Item1', 'DIA2', 'LOC4', 'ID4', 90, 'NTA5', 77), ('Item1', 'DIA2', 'LOC4', 'ID5', 23, 'NTA5', 23), ('Item1', 'DIA3', 'LOC5', 'ID6', 50, 'NTA6', 50), ('Item1', 'DIA3', 'LOC6', 'ID7', 20, 'NTA6', 20), ('Item2', 'DIA1', 'LOC4', 'ID8', 44, 'NTA7', 22), ('Item2', 'DIA1', 'LOC4', 'ID8', 44, 'NTA8', 20), ('Item2', 'DIA2', 'LOC5', 'ID8', 21, 'NTA9', 6), ('Item2', 'DIA2', 'LOC5', 'ID8', 21, 'NTA10', 15), ('Item2', 'DIA3', 'LOC6', 'ID9', 20, 'NTA11', 8), ('Item2', 'DIA3', 'LOC6', 'ID9', 20, 'NTA11', 12) ; 

Below is a screenshot;

I can do this with proc and follow the cursor, but I want there to be an easy way with SQL 2014, and I know that the fact that caused the CTE trick will help.

Could you share your decision? Appreciate a lot of your valuable ideas.

Answer 1

You just need to explode the quantities in units for both table 1 and table2, and then combine them side by side.
Pay attention to FN_NUMBERS (n), this is a function that returns only one column with numbers from 1 to n, you need it in your database, there are many ways to do this, just google for the “table tables” or see here .
I am using the following:

 CREATE FUNCTION FN_NUMBERS( @MAX INT ) RETURNS @N TABLE (N INT NOT NULL PRIMARY KEY) BEGIN WITH Pass0 as (select '1' as C union all select '1'), --2 rows Pass1 as (select '1' as C from Pass0 as A, Pass0 as B),--4 rows Pass2 as (select '1' as C from Pass1 as A, Pass1 as B),--16 rows Pass3 as (select '1' as C from Pass2 as A, Pass2 as B),--256 rows Pass4 as (select TOP (@MAX) '1' as C from Pass3 as A, Pass3 as B) --65536 rows ,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass2 as B, Pass1 as C) --4194304 rows --,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass3 as B) --16777216 rows --,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass4 as B) --4294836225 rows INSERT INTO @N SELECT TOP (@MAX) ROW_NUMBER() OVER(ORDER BY C) AS N FROM Tally RETURN END 

Return to sql ..

 ;with t1 as ( select *, ROW_NUMBER() over (partition by itm,dia order by loc,id) rn from table1 t1 join FN_NUMBERS(500) on n<=t1.qty ), t2 as ( select *, ROW_NUMBER() over (partition by itm,dia order by nta) rn from table2 t2 join FN_NUMBERS(500) on n<=t2.qty ), t3 as ( select t1.itm, t1.dia, t1.loc, t1.id, t1.qty, t2.nta, count(t1.n) NewQTY from t1 join t2 on t1.itm=t2.itm and t1.dia = t2.dia and t1.rn=t2.rn group by t1.itm, t1.dia, t1.loc, t1.id, t1.qty, t2.nta ) select * from t3 order by 1,2,3,4,5,6