SQL Server 2022

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

DECLARE @Table TABLE(
  PRODUCTNUMBER VARCHAR(10),
  PURCHASEORDERNUMBER VARCHAR(11),
  INSPECTIONDATE date,
  BOOKINGTYPEDesc varchar(20),
  RESULT VARCHAR(10),
  PRIMARY KEY (PRODUCTNUMBER,PURCHASEORDERNUMBER,INSPECTIONDATE));

INSERT INTO @table(PRODUCTNUMBER,PURCHASEORDERNUMBER,INSPECTIONDATE,BOOKINGTYPEDesc,RESULT) 
VALUES 
('117858-EUC',  '400P0003270',  '2023-04-27','FirstInspection','Pass'),
('117858-EUC',  '400P0003270',  '2023-04-29','FirstInspection', 'Fail'),
('117858-EUC',  '400P0003270',  '2023-05-02','SecondInspection',    'Reject'),
('117858-EUC',  '400P0003270',  '2023-05-15','FirstInspection', 'Abort'),
('117858-EUC',  '400P0003270',  '2023-05-20','FirstInspection', 'Pass'),
('117858-EUC',  '400P0003270',  '2023-05-21','SecondInspection',    'Fail'),
('117858-EUC',  '400P0003327',  '2023-04-27','FirstInspection', 'Pass'),
('117858-EUC',  '400P0003327',  '2023-04-28','FirstInspection', 'Pass'),
('117858-EUC',  '400P0003327',  '2023-04-29','FirstInspection', 'Pass'),
('117858-EUC',  '400P0003327',  '2023-05-01','FirstInspection', 'Fail'),
('117858-EUC',  '400P0003327',  '2023-05-03','Second',  'Fail'),
('117858-EUC',  '400P0003327',  '2023-05-09','FirstInspection', 'Reject'),
('117858-EUC',  '400P0003327',  '2023-05-12','Second',  'Pass'),
('117858-EUC',  '400P0003327',  '2023-05-15','FirstInspection', 'Pass');

WITH cte AS (
    SELECT
      PRODUCTNUMBER, PURCHASEORDERNUMBER, INSPECTIONDATE, BOOKINGTYPEDesc, RESULT,
      ResultSql = CAST(RESULT AS varchar(max))
    FROM (
        SELECT *,
          rn = ROW_NUMBER() OVER (PARTITION BY PRODUCTNUMBER, PURCHASEORDERNUMBER ORDER BY INSPECTIONDATE)
        FROM @table t
    ) t
    WHERE t.rn = 1

UNION ALL

SELECT
      t.PRODUCTNUMBER, t.PURCHASEORDERNUMBER, t.INSPECTIONDATE, t.BOOKINGTYPEDesc, t.RESULT,
      CONCAT(cte.ResultSql, ', ', t.RESULT)
    FROM cte
    CROSS APPLY (
        SELECT *,
          rn = ROW_NUMBER() OVER (PARTITION BY PRODUCTNUMBER, PURCHASEORDERNUMBER ORDER BY INSPECTIONDATE)
        FROM @table t
        WHERE t.PRODUCTNUMBER = cte.PRODUCTNUMBER
          AND t.PURCHASEORDERNUMBER = cte.PURCHASEORDERNUMBER
          AND t.INSPECTIONDATE > cte.INSPECTIONDATE
    ) t
    WHERE t.rn = 1
)
SELECT *
FROM cte;

PRODUCTNUMBER	PURCHASEORDERNUMBER	INSPECTIONDATE	BOOKINGTYPEDesc	RESULT	ResultSql
117858-EUC	400P0003270	2023-04-27	FirstInspection	Pass	Pass
117858-EUC	400P0003327	2023-04-27	FirstInspection	Pass	Pass
117858-EUC	400P0003327	2023-04-28	FirstInspection	Pass	Pass, Pass
117858-EUC	400P0003327	2023-04-29	FirstInspection	Pass	Pass, Pass, Pass
117858-EUC	400P0003327	2023-05-01	FirstInspection	Fail	Pass, Pass, Pass, Fail
117858-EUC	400P0003327	2023-05-03	Second	Fail	Pass, Pass, Pass, Fail, Fail
117858-EUC	400P0003327	2023-05-09	FirstInspection	Reject	Pass, Pass, Pass, Fail, Fail, Reject
117858-EUC	400P0003327	2023-05-12	Second	Pass	Pass, Pass, Pass, Fail, Fail, Reject, Pass
117858-EUC	400P0003327	2023-05-15	FirstInspection	Pass	Pass, Pass, Pass, Fail, Fail, Reject, Pass, Pass
117858-EUC	400P0003270	2023-04-29	FirstInspection	Fail	Pass, Fail
117858-EUC	400P0003270	2023-05-02	SecondInspection	Reject	Pass, Fail, Reject
117858-EUC	400P0003270	2023-05-15	FirstInspection	Abort	Pass, Fail, Reject, Abort
117858-EUC	400P0003270	2023-05-20	FirstInspection	Pass	Pass, Fail, Reject, Abort, Pass
117858-EUC	400P0003270	2023-05-21	SecondInspection	Fail	Pass, Fail, Reject, Abort, Pass, Fail