SQL concatenate row by row by date order with in group

Question

I am looking for a way to concatenate the rows into a comma separated string.

Example:

I am looking for result as below where Result Seq column should concatenate the result column row by row values in order of Inspectiondate within group of ProductNumber, Purchaseordernumber.

DECLARE @Table TABLE(PRODUCTNUMBER VARCHAR(10),PURCHASEORDERNUMBER 
VARCHAR(11), INSPECTIONDATE date,BOOKINGTYPEDesc varchar(20),RESULT 
VARCHAR(10));
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')

Any help on the SQL query.

Answer 1

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

Answer 2

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:

Answer 3

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