SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Create the Orders table
CREATE TABLE Orders (
  OrderStatus VARCHAR(50),
  CustomerID INT,
  OrderNbr INT,
  LoadNbr INT,
  Product VARCHAR(50),
  Quantity INT
);

-- Insert sample data into the Orders table
INSERT INTO Orders (OrderStatus, CustomerID, OrderNbr, LoadNbr, Product, Quantity)
VALUES
  ('Pending', 1, 1, 1, 'Product A', 10),
  ('Pending', 1, 1, 2, 'Product B', 20),
  ('Pending', 1, 1, 3, 'Product C', 30),
  ('Pending', 1, 1, 4, 'Product D', 40),
  ('Pending', 1, 1, 5, 'Product E', 50),
  ('Pending', 1, 1, 6, 'Product F', 60),
  ('Pending', 1, 1, 7, 'Product G', 70),
  ('Pending', 1, 1, 8, 'Product H', 80)
8 rows affected

WITH OrderSummary
AS (
    SELECT O.OrderStatus
        , O.CustomerID
        , O.OrderNbr
        , 'TotalQty' = O.Quantity + I2.Quantity + I3.Quantity + I4.Quantity 
                    + I5.Quantity + I6.Quantity + I7.Quantity + I8.Quantity
        ,     'Prod01' = O.Product
        , 'Quantity01' = O.Quantity
        ,     'Prod02' = I2.Product
        , 'Quantity02' = I2.Quantity
        ,     'Prod03' = I3.Product
        , 'Quantity03' = I3.Quantity
        ,     'Prod04' = I4.Product
        , 'Quantity04' = I4.Quantity
        ,     'Prod05' = I5.Product
        , 'Quantity05' = I5.Quantity
        ,     'Prod06' = I6.Product
        , 'Quantity06' = I6.Quantity
        ,     'Prod07' = I7.Product
        , 'Quantity07' = I7.Quantity
        ,     'Prod08' = I8.Product
        , 'Quantity08' = I8.Quantity
    FROM Orders O
    LEFT JOIN Orders I2 ON O.OrderNbr = I2.OrderNbr AND I2.LoadNbr = 2
    LEFT JOIN Orders I3 ON O.OrderNbr = I3.OrderNbr AND I3.LoadNbr = 3
    LEFT JOIN Orders I4 ON O.OrderNbr = I4.OrderNbr AND I4.LoadNbr = 4
    LEFT JOIN Orders I5 ON O.OrderNbr = I5.OrderNbr AND I5.LoadNbr = 5
    LEFT JOIN Orders I6 ON O.OrderNbr = I6.OrderNbr AND I6.LoadNbr = 6
    LEFT JOIN Orders I7 ON O.OrderNbr = I7.OrderNbr AND I7.LoadNbr = 7
    LEFT JOIN Orders I8 ON O.OrderNbr = I8.OrderNbr AND I8.LoadNbr = 8
    WHERE O.LoadNbr = 1
    )
SELECT *
    , 'Ratio01' = Quantity01 / TotalQty * 100
    , 'Ratio02' = Quantity02 / TotalQty * 100
    , 'Ratio03' = Quantity03 / TotalQty * 100
    , 'Ratio04' = Quantity04 / TotalQty * 100
    , 'Ratio05' = Quantity05 / TotalQty * 100
    , 'Ratio06' = Quantity06 / TotalQty * 100
    , 'Ratio07' = Quantity07 / TotalQty * 100
    , 'Ratio08' = Quantity08 / TotalQty * 100
FROM OrderSummary


OrderStatus
CustomerID
OrderNbr
TotalQty
Prod01
Quantity01
Prod02
Quantity02
Prod03
Quantity03
Prod04
Quantity04
Prod05
Quantity05
Prod06
Quantity06
Prod07
Quantity07
Prod08
Quantity08
Ratio01
Ratio02
Ratio03
Ratio04
Ratio05
Ratio06
Ratio07
Ratio08


Pending
1
1
360
Product A
10
Product B
20
Product C
30
Product D
40
Product E
50
Product F
60
Product G
70
Product H
80
0
0
0
0
0
0
0
0


DECLARE @cols AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT ', max(CASE WHEN o.LoadNbr = ' 
  + CONVERT(VARCHAR(10), LoadNbr) + ' THEN o.Product ELSE '''' END) AS Product' 
  + CONVERT(VARCHAR(10), LoadNbr) + ', max(CASE WHEN o.LoadNbr = ' 
  + CONVERT(VARCHAR(10), LoadNbr) + ' THEN o.Quantity ELSE 0 END) AS Quantity' 
  + CONVERT(VARCHAR(10), LoadNbr) + ', max(CASE WHEN o.LoadNbr = ' 
  + CONVERT(VARCHAR(10), LoadNbr) + ' THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio' 
  + CONVERT(VARCHAR(10), LoadNbr)
             FROM Orders
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,2,'');

select @cols;

SET @sql = 'SELECT OrderStatus
    , CustomerID
    , OrderNbr
    , ca.TotalQty
    , ' + @cols + '
FROM Orders AS O
CROSS APPLY (
    SELECT SUM(q.Quantity) AS TotalQty FROM Orders AS q
    WHERE q.OrderNbr = o.OrderNbr
    ) AS ca
GROUP BY OrderStatus
    , CustomerID
    , OrderNbr
    , ca.TotalQty';

select @sql;

EXEC sp_executesql @sql;


(No column name)


max(CASE WHEN o.LoadNbr = 1 THEN o.Product ELSE '' END) AS Product1, max(CASE WHEN o.LoadNbr = 1 THEN o.Quantity ELSE 0 END) AS Quantity1, max(CASE WHEN o.LoadNbr = 1 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio1, max(CASE WHEN o.LoadNbr = 2 THEN o.Product ELSE '' END) AS Product2, max(CASE WHEN o.LoadNbr = 2 THEN o.Quantity ELSE 0 END) AS Quantity2, max(CASE WHEN o.LoadNbr = 2 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio2, max(CASE WHEN o.LoadNbr = 3 THEN o.Product ELSE '' END) AS Product3, max(CASE WHEN o.LoadNbr = 3 THEN o.Quantity ELSE 0 END) AS Quantity3, max(CASE WHEN o.LoadNbr = 3 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio3, max(CASE WHEN o.LoadNbr = 4 THEN o.Product ELSE '' END) AS Product4, max(CASE WHEN o.LoadNbr = 4 THEN o.Quantity ELSE 0 END) AS Quantity4, max(CASE WHEN o.LoadNbr = 4 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio4, max(CASE WHEN o.LoadNbr = 5 THEN o.Product ELSE '' END) AS Product5, max(CASE WHEN o.LoadNbr = 5 THEN o.Quantity ELSE 0 END) AS Quantity5, max(CASE WHEN o.LoadNbr = 5 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio5, max(CASE WHEN o.LoadNbr = 6 THEN o.Product ELSE '' END) AS Product6, max(CASE WHEN o.LoadNbr = 6 THEN o.Quantity ELSE 0 END) AS Quantity6, max(CASE WHEN o.LoadNbr = 6 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio6, max(CASE WHEN o.LoadNbr = 7 THEN o.Product ELSE '' END) AS Product7, max(CASE WHEN o.LoadNbr = 7 THEN o.Quantity ELSE 0 END) AS Quantity7, max(CASE WHEN o.LoadNbr = 7 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio7, max(CASE WHEN o.LoadNbr = 8 THEN o.Product ELSE '' END) AS Product8, max(CASE WHEN o.LoadNbr = 8 THEN o.Quantity ELSE 0 END) AS Quantity8, max(CASE WHEN o.LoadNbr = 8 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio8




(No column name)


SELECT OrderStatus
    , CustomerID
    , OrderNbr
    , ca.TotalQty
    , max(CASE WHEN o.LoadNbr = 1 THEN o.Product ELSE '' END) AS Product1, max(CASE WHEN o.LoadNbr = 1 THEN o.Quantity ELSE 0 END) AS Quantity1, max(CASE WHEN o.LoadNbr = 1 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio1, max(CASE WHEN o.LoadNbr = 2 THEN o.Product ELSE '' END) AS Product2, max(CASE WHEN o.LoadNbr = 2 THEN o.Quantity ELSE 0 END) AS Quantity2, max(CASE WHEN o.LoadNbr = 2 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio2, max(CASE WHEN o.LoadNbr = 3 THEN o.Product ELSE '' END) AS Product3, max(CASE WHEN o.LoadNbr = 3 THEN o.Quantity ELSE 0 END) AS Quantity3, max(CASE WHEN o.LoadNbr = 3 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio3, max(CASE WHEN o.LoadNbr = 4 THEN o.Product ELSE '' END) AS Product4, max(CASE WHEN o.LoadNbr = 4 THEN o.Quantity ELSE 0 END) AS Quantity4, max(CASE WHEN o.LoadNbr = 4 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio4, max(CASE WHEN o.LoadNbr = 5 THEN o.Product ELSE '' END) AS Product5, max(CASE WHEN o.LoadNbr = 5 THEN o.Quantity ELSE 0 END) AS Quantity5, max(CASE WHEN o.LoadNbr = 5 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio5, max(CASE WHEN o.LoadNbr = 6 THEN o.Product ELSE '' END) AS Product6, max(CASE WHEN o.LoadNbr = 6 THEN o.Quantity ELSE 0 END) AS Quantity6, max(CASE WHEN o.LoadNbr = 6 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio6, max(CASE WHEN o.LoadNbr = 7 THEN o.Product ELSE '' END) AS Product7, max(CASE WHEN o.LoadNbr = 7 THEN o.Quantity ELSE 0 END) AS Quantity7, max(CASE WHEN o.LoadNbr = 7 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio7, max(CASE WHEN o.LoadNbr = 8 THEN o.Product ELSE '' END) AS Product8, max(CASE WHEN o.LoadNbr = 8 THEN o.Quantity ELSE 0 END) AS Quantity8, max(CASE WHEN o.LoadNbr = 8 THEN round(o.Quantity *100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio8
FROM Orders AS O
CROSS APPLY (
    SELECT SUM(q.Quantity) AS TotalQty FROM Orders AS q
    WHERE q.OrderNbr = o.OrderNbr
    ) AS ca
GROUP BY OrderStatus
    , CustomerID
    , OrderNbr
    , ca.TotalQty




OrderStatus
CustomerID
OrderNbr
TotalQty
Product1
Quantity1
Ratio1
Product2
Quantity2
Ratio2
Product3
Quantity3
Ratio3
Product4
Quantity4
Ratio4
Product5
Quantity5
Ratio5
Product6
Quantity6
Ratio6
Product7
Quantity7
Ratio7
Product8
Quantity8
Ratio8


Pending
1
1
360
Product A
10
2.780000000000
Product B
20
5.560000000000
Product C
30
8.330000000000
Product D
40
11.110000000000
Product E
50
13.890000000000
Product F
60
16.670000000000
Product G
70
19.440000000000
Product H
80
22.220000000000


-- "pivot" is just too limited IMHO

SELECT *
FROM (
  SELECT OrderStatus, CustomerID, OrderNbr, CONCAT('LoadNbr', LoadNbr) as LoadNbr, Product, Quantity
  FROM Orders
) t
PIVOT (
  SUM(Quantity)
  FOR LoadNbr IN ([LoadNbr1],[LoadNbr2],[LoadNbr3],[LoadNbr4],[LoadNbr5],[LoadNbr6],[LoadNbr7],[LoadNbr8])
) p


OrderStatus
CustomerID
OrderNbr
Product
LoadNbr1
LoadNbr2
LoadNbr3
LoadNbr4
LoadNbr5
LoadNbr6
LoadNbr7
LoadNbr8


Pending
1
1
Product A
10
null
null
null
null
null
null
null

Pending
1
1
Product B
null
20
null
null
null
null
null
null

Pending
1
1
Product C
null
null
30
null
null
null
null
null

Pending
1
1
Product D
null
null
null
40
null
null
null
null

Pending
1
1
Product E
null
null
null
null
50
null
null
null

Pending
1
1
Product F
null
null
null
null
null
60
null
null

Pending
1
1
Product G
null
null
null
null
null
null
70
null

Pending
1
1
Product H
null
null
null
null
null
null
null
80
OrderStatus	CustomerID	OrderNbr	Product	LoadNbr1	LoadNbr2	LoadNbr3	LoadNbr4	LoadNbr5	LoadNbr6	LoadNbr7	LoadNbr8
Pending	1	1	Product A	10	null	null	null	null	null	null	null
Pending	1	1	Product B	null	20	null	null	null	null	null	null
Pending	1	1	Product C	null	null	30	null	null	null	null	null
Pending	1	1	Product D	null	null	null	40	null	null	null	null
Pending	1	1	Product E	null	null	null	null	50	null	null	null
Pending	1	1	Product F	null	null	null	null	null	60	null	null
Pending	1	1	Product G	null	null	null	null	null	null	70	null
Pending	1	1	Product H	null	null	null	null	null	null	null	80