Dear experts, I'm lost, please help me.
I have a table with BOM Positions:
BomId MaterialId MaterialQuantity Note
----- ---------- ---------------- -----
Bom_A Mat_A 20 BOM1
Bom_A Mat_B 30 BOM1
Bom_B Mat_A 20 BOM2
Bom_B Mat_B 35 BOM2
Bom_C Mat_A 20 BOM1
Bom_C Mat_B 30 BOM1
Bom_D Mat_A 10 BOM3
Bom_D Mat_B 20 BOM3
Bom_D Mat_C 30 BOM3
Bom_E Mat_A 10 BOM3
Bom_E Mat_B 20 BOM3
Bom_E Mat_C 30 BOM3
Based on that table I like to select distinct the equal BOMs.
Equal here means the BomDetails have same material in same quantities.
Note: The column 'Note' is only for the explanation here and does not exists in my db.
Therefore the result I'm looking for is something like this:
MaterialId MaterialQuantity BOMGRP BOMCOUNT
---------- ---------------- ------ ---------
Mat_A 20 1 2
Mat_B 30 1 2
Mat_A 20 2 1
Mat_B 35 2 1
Mat_A 10 3 2
Mat_B 20 3 2
Mat_C 30 3 2
'BOMGRP' should be a dynamically created Id.
'BOMCOUNT' would be nice, but I can live without it
I hope my question makes sense.
Thank you very much in advance.
What I have tried:
I tried to solve it with the help of ChatGPT, but without success.
Btw. I'm on MS SQL Server 2014 :(
[Edit]: Upgraded to MS SQL Server 2019 :)
DROP TABLE BomPositions;
CREATE TABLE BomPositions
(
BomId VARCHAR(30),
MaterialId VARCHAR(30),
MaterialQuantity DOUBLE PRECISION,
Note VARCHAR(30)
);
INSERT INTO BomPositions(BomId, MaterialId, MaterialQuantity, Note) VALUES
('Bom_A', 'Mat_A', 20, 'BOM1'),
('Bom_A', 'Mat_B', 30, 'BOM1'),
('Bom_B', 'Mat_A', 20, 'BOM2'),
('Bom_B', 'Mat_B', 35, 'BOM2'),
('Bom_C', 'Mat_A', 20, 'BOM1'),
('Bom_C', 'Mat_B', 30, 'BOM1'),
('Bom_D', 'Mat_A', 10, 'BOM3'),
('Bom_D', 'Mat_B', 20, 'BOM3'),
('Bom_D', 'Mat_C', 30, 'BOM3'),
('Bom_E', 'Mat_A', 10, 'BOM3'),
('Bom_E', 'Mat_B', 20, 'BOM3'),
('Bom_E', 'Mat_C', 30, 'BOM3');
SELECT * FROM BomPositions ORDER BY BomId, MaterialId;