SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SEGMENT (
  SEGMENTID INT NOT NULL,
  SEGMENTID_FK INT,
  SEGMENTPARTSID INT NOT NULL,
  PRIMARY KEY (SEGMENTID)
);

CREATE TABLE SEGMENTPART (
  SEGMENTPARTSID INT NOT NULL,
  SEGMENTTYPEID INT NOT NULL,
  NAME VARCHAR(200) NOT NULL,
  SERIALNUMBER VARCHAR(150),
  WEIGHT DECIMAL(14,4),
  PRIMARY KEY (SEGMENTPARTSID)
);
INSERT INTO SEGMENT VALUES (1, NULL, 1);
INSERT INTO SEGMENT VALUES (2, 1, 2);
INSERT INTO SEGMENT VALUES (3, 1, 3);
INSERT INTO SEGMENT VALUES (4, 2, 4);
INSERT INTO SEGMENT VALUES (5, 2, 5);
INSERT INTO SEGMENT VALUES (6, 3, 6);

INSERT INTO SEGMENTPART VALUES (1, 101, 'Component 1', 'SN001', 10.5);
INSERT INTO SEGMENTPART VALUES (2, 102, 'Component 2', 'SN002', 15.2);
INSERT INTO SEGMENTPART VALUES (3, 103, 'Component 3', 'SN003', 20.3);
INSERT INTO SEGMENTPART VALUES (4, 104, 'Component 4', 'SN004', 8.7);
INSERT INTO SEGMENTPART VALUES (5, 105, 'Component 5', 'SN005', 12.8);
INSERT INTO SEGMENTPART VALUES (6, 106, 'Component 6', 'SN006', 40.8);
12 rows affected

WITH RecursiveCTE AS (
    SELECT 
        s.SEGMENTID, 
        s.SEGMENTID AS RootSegmentID, -- You need to keep tracking of the root segment ID
        sp.WEIGHT
    FROM SEGMENT s
    INNER JOIN SEGMENTPART sp ON s.SEGMENTPARTSID = sp.SEGMENTPARTSID
    UNION ALL
    -- Recursive part: here you find and accumulate weights for child segments
    SELECT 
        s.SEGMENTID, 
        r.RootSegmentID, -- It is the same throughout the recursion to track back to the root
        sp.WEIGHT
    FROM SEGMENT s
    INNER JOIN RecursiveCTE r ON s.SEGMENTID_FK = r.SEGMENTID
    INNER JOIN SEGMENTPART sp ON s.SEGMENTPARTSID = sp.SEGMENTPARTSID
)
SELECT 
    RootSegmentID AS SEGMENTID, 
    SUM(WEIGHT) AS Total_weight
FROM RecursiveCTE
GROUP BY RootSegmentID
ORDER BY RootSegmentID;


SEGMENTID
Total_weight


1
108.3000

2
36.7000

3
61.1000

4
8.7000

5
12.8000

6
40.8000