If you want to generate a ResultSeq column which concatenates the values in the Result column, row by row, ordered by Inspectiondate , partitioned by ProductNumber and Purchaseordernumber , then the following approach is an option:

SELECT 
   PRODUCTNUMBER, PURCHASEORDERNUMBER, INSPECTIONDATE, RESULT,
   RESULTSEQ = (
      SELECT STRING_AGG(RESULT, ',') WITHIN GROUP (ORDER BY INSPECTIONDATE)
      FROM @table 
      WHERE 
         (PRODUCTNUMBER = t.PRODUCTNUMBER) AND
         (PURCHASEORDERNUMBER = t.PURCHASEORDERNUMBER) AND
         (INSPECTIONDATE <= t.INSPECTIONDATE)
   )
FROM @table t
ORDER BY PRODUCTNUMBER, PURCHASEORDERNUMBER, INSPECTIONDATE

Thanks for all your inputs. with the help of the links you have given,

I have managed to get the output which i am looking for. SQL Query:

SELECT mt.*,
STUFF((  
    SELECT ', ' + Result 
    FROM @table t
    WHERE t.INSPECTIONDATE <= mt.INSPECTIONDATE  and t.PRODUCTNUMBER = mt.PRODUCTNUMBER and t.PURCHASEORDERNUMBER = mt.PURCHASEORDERNUMBER
    and t.BOOKINGTYPEDesc = 'FirstInspection'
    FOR XML PATH('')), 1, 2, '') AS Result_Seq
FROM @table mt

Output:

Another option is a recursive CTE.

• Begin by selecting the first row for each group. We can do this using ROW_NUMBER .

• For each row in the recursive section, get the next "first row" starting from after our current one.

• We must use ROW_NUMBER again because TOP is not allowed in a rCTE.

• The compiler can optimize away the row-numbering into a TOP .

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;

db<>fiddle