SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Products (
ArticleNo NVARCHAR(50) NULL,
Name NVARCHAR(50) NULL,
Unit_Package NVARCHAR(50) NULL,
Order_Package NVARCHAR(50) NULL,
Customer_Package NVARCHAR(50) NULL);

INSERT INTO Products (ArticleNo,Name,Unit_Package,Order_Package,Customer_Package) VALUES
('A0001','PXESX','CUT','CRT','PC'),
('A0002','GASE','PGK','CRT','PCE'),
('A0003','DORR','FCG','FCK','PC');

SELECT *
FROM Products;


ArticleNo
Name
Unit_Package
Order_Package
Customer_Package


A0001
PXESX
CUT
CRT
PC

A0002
GASE
PGK
CRT
PCE

A0003
DORR
FCG
FCK
PC


CREATE TABLE Package (
ArticleNo NVARCHAR(50) NULL,
Package NVARCHAR(50) NULL,
Quantity INT NULL);

INSERT INTO Package (ArticleNo,Package,Quantity) VALUES
('A0001','CUT',2),
('A0001','CRT',3),
('A0001','PC',4),
('A0001','PGK',5),
('A0002','CUT',34),
('A0002','PGK',22),
('A0002','CRT',3),
('A0002','PCE',3),
('A0003','GG',3),
('A0003','FCG',2),
('A0003','FCK',1),
('A0003','PC',7);

SELECT *
FROM Package;


ArticleNo
Package
Quantity


A0001
CUT
2

A0001
CRT
3

A0001
PC
4

A0001
PGK
5

A0002
CUT
34

A0002
PGK
22

A0002
CRT
3

A0002
PCE
3

A0003
GG
3

A0003
FCG
2

A0003
FCK
1

A0003
PC
7


SELECT A.ArticleNo,
       A.Name,
       A.Unit_Package,
       SUM(IIF(B.Package = Unit_Package,B.Quantity,0)) AS Unit_Qty,
       A.Order_Package,
       SUM(IIF(B.Package = Order_Package,B.Quantity,0)) AS Order_Qty,
       A.Customer_Package,
       SUM(IIF(B.Package = Customer_Package,B.Quantity,0)) AS Customer_Qty
FROM Products A
LEFT JOIN Package B ON B.ArticleNo=A.ArticleNo AND B.Package IN (A.Unit_Package,A.Order_Package,A.Customer_Package)
GROUP BY 
      A.ArticleNo,
      A.Name,
      A.Unit_Package,
      A.Order_Package,
      A.Customer_Package


ArticleNo
Name
Unit_Package
Unit_Qty
Order_Package
Order_Qty
Customer_Package
Customer_Qty


A0001
PXESX
CUT
2
CRT
3
PC
4

A0002
GASE
PGK
22
CRT
3
PCE
3

A0003
DORR
FCG
2
FCK
1
PC
7

ArticleNo	Package	Quantity
A0001	CUT	2
A0001	CRT	3
A0001	PC	4
A0001	PGK	5
A0002	CUT	34
A0002	PGK	22
A0002	CRT	3
A0002	PCE	3
A0003	GG	3
A0003	FCG	2
A0003	FCK	1
A0003	PC	7

ArticleNo	Name	Unit_Package	Unit_Qty	Order_Package	Order_Qty	Customer_Package	Customer_Qty
A0001	PXESX	CUT	2	CRT	3	PC	4
A0002	GASE	PGK	22	CRT	3	PCE	3
A0003	DORR	FCG	2	FCK	1	PC	7