SQL how to split Quantity into Multiple Rows based on Quantity on Target source

Question

I have two table where i want to join and show quantity with details. table are join with ITM,DIA , and total Qty is equal in both table on ITM/DIA combination

I want to split table2 quantity on table1 and populate table2 data along with table1 data.

I have below data for your reference, "table1" and "table2". and you can see my expected result in 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 screenshot of data;

I can make it with a proc and follow cursor, but I want to is there any easy way with SQL 2014 and I know for a fact CTE recusive trick will help..

Could you please share your solution on this? appreciate lot on your valuable ideas..

Which DBMS are you using? Also: http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Oct 18 '16 at 08:41
@a_horse_with_no_name From the last line I take it's Sql-Server-2014 — Shnugo, Oct 18 '16 at 08:46
@Shnugo: but the DDL in the question is invalid for SQL Server — , Oct 18 '16 at 08:47
@a_horse_with_no_name Ah, I see, there are back-ticks... So please dear user459295, clearify the DBMS (vendor and version) and correct the tags if I did this wrong... — Shnugo, Oct 18 '16 at 08:49
@Shnugo Thanks for editing my post, i was not aware how to draft like that, Tags are fine now. i am trying in SQL Server 2014.I hope you all are clear about my question. — user459295, Oct 18 '16 at 09:21
Yet got no solution but possible way could be quirky update. — Esty, Oct 18 '16 at 09:34
I tried to understand this - but sorry... Could you please elaborate the logic how you want to get them together? — Shnugo, Oct 18 '16 at 10:11
@Shnugo Thanks for coming back on this. Simply I want to populate **NTA and Qty from table2 to table1**. Between these two table QTY is matching based on the **combination of ITM and DIA cols**. but it is **not one to one relation**, line count will be very between these two tables.eg> table1 has 3 rows for Item1 and DIA1, but 2 rows in table2, so I need to split 2 rows into table1 as showing in tableResult. **check Qty col in table1 and newQty in tableResult**. Let me know if you understood my scenario. — user459295, Oct 18 '16 at 10:25
@Shnugo , to clarify further, take combination of Item1/DIA from table1. table1 has 3 rows and total Qty is 13. also take same combination from table2, so total is matching and is equal to 13. Now, I need to split that 13 from tabel2 along with NTA1(10) and NTA2(3) data.So, lets put NTA1 - 10 from table2 to first line, it is Qty-3 , so I can put 3 from NTA1 and remaining goes to 2nd line of table1 which 4, so lets keep 4 from NTA1 again, now remaining is 3, then 3rd line of table1, it has Qty-6 but can not fulfill with NTA1 - 3, need more NTA data. _(read next comments)_ — user459295, Oct 18 '16 at 11:01
@Shnugo So, lets put remaing Qty-3 from NTA1 and take NTA2 - 3 to fulfill 3rd line of table1, since tht line take from two NTAs (NTA1, NTA2) , we need to add new line and put new Qty. then together there will be 4 lines on result for the combination of Item1/DIA1. hope you got it. — user459295, Oct 18 '16 at 11:04

score 1 · Answer 1 · edited May 23 '17 at 11:59

You simply need to explode quantities in units both for table1 and table2 and then couple them side by side.
Pay attention to FN_NUMBERS(n), it is a function that returns only one column with numbers from 1 to n, you need it in you database, there are many ways to do it, just google for "tally tables" or look here.
I use 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

Back to the 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

SQL how to split Quantity into Multiple Rows based on Quantity on Target source

1 Answers1

Linked