SQL Server 2022

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

Create table aa (
X1 NVarchar(10),
X2 NVarchar(10),
X3 NVarchar(10),
X4 Float);

Insert Into aa Values
('B','02',N'螺絲-全',1300),
('B','02',N'螺絲-次',5),
('A','02',N'螺絲-次',4),
('A','02',N'螺絲-全',1200),
('A','01',N'螺絲-次',2),
('A','01',N'螺絲-全',1000),
('B','01',N'螺絲-次',3),
('B','01',N'螺絲-全',1100);

Select * From aa
Order By X2,X1,X3 desc;


X1
X2
X3
X4


A
01
螺絲-次
2

A
01
螺絲-全
1000

B
01
螺絲-次
3

B
01
螺絲-全
1100

A
02
螺絲-次
4

A
02
螺絲-全
1200

B
02
螺絲-次
5

B
02
螺絲-全
1300


Select *
From (
Select X1,X2,X3,X4 
From aa
Union 
Select T1.X1 AS X1,T1.X2 AS X2,N'螺絲-率' AS X3,
Round(T1.X4 / T2.X4,6) AS X4
From aa AS T1  
Left Join aa AS T2 On T1.X1 = T2.X1 And T1.X2 = T2.X2
Where T1.X3 = N'螺絲-次'
And T2.X3 = N'螺絲-全') AS MM
--
Order By X2,X1,(Case When X3 = N'螺絲-次' then 1
                     When X3 = N'螺絲-全' then 2
                     When X3 = N'螺絲-率' then 3
                     Else 4 End)


X1
X2
X3
X4


A
01
螺絲-次
2

A
01
螺絲-全
1000

A
01
螺絲-率
0.002

B
01
螺絲-次
3

B
01
螺絲-全
1100

B
01
螺絲-率
0.002727

A
02
螺絲-次
4

A
02
螺絲-全
1200

A
02
螺絲-率
0.003333

B
02
螺絲-次
5

B
02
螺絲-全
1300

B
02
螺絲-率
0.003846


Select *
From (
Select X1,X2,X3,X4 
From aa
Union 
Select T1.X1 AS X1,T1.X2 AS X2,N'螺絲-率' AS X3,
Round(T1.X4 / T2.X4,6) AS X4
From aa AS T1  
Left Join aa AS T2 On T1.X1 = T2.X1 And T1.X2 = T2.X2
Where T1.X3 = N'螺絲-次'
And T2.X3 = N'螺絲-全') AS MM
--
PIVOT ( 
SUM(X4) 
FOR X2 IN ([01],[02]) 
) AS QQ
Order By X1,(Case When X3 = N'螺絲-次' then 1
                  When X3 = N'螺絲-全' then 2
                  When X3 = N'螺絲-率' then 3
                  Else 4 End)


X1
X3
01
02


A
螺絲-次
2
4

A
螺絲-全
1000
1200

A
螺絲-率
0.002
0.003333

B
螺絲-次
3
5

B
螺絲-全
1100
1300

B
螺絲-率
0.002727
0.003846

X1	X2	X3	X4
A	01	螺絲-次	2
A	01	螺絲-全	1000
B	01	螺絲-次	3
B	01	螺絲-全	1100
A	02	螺絲-次	4
A	02	螺絲-全	1200
B	02	螺絲-次	5
B	02	螺絲-全	1300