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.

[Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql), [How do I create a comma-separated list using a SQL query?](https://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query), [Multiple rows to one comma-separated value in Sql Server \[duplicate\]](https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value-in-sql-server), [SQL convert column to comma separated row](https://stackoverflow.com/questions/68860263/sql-convert-column-to-comma-separated-row), etc, etc, etc. — Thom A, Jun 20 '23 at 09:06
There are literally 100's of duplicates for this; take your pick. — Thom A, Jun 20 '23 at 09:07
@ThomA This one looks different, they don't actually want to aggregate, more of a `STRING_AGG() OVER (ORDER BY ...` which is not available as a window function. — Charlieface, Jun 20 '23 at 09:36
You could with a triangular join, @Charlieface , though not ideal. — Thom A, Jun 20 '23 at 09:39
@ThomA Very unideal, probably better to use a rCTE, or possibly a bunch of `LAG` functions. Either way, not a dupe of what you mention. — Charlieface, Jun 20 '23 at 09:40
Fyi please don't use images for data - use formatted tabular text — Dale K, Jun 20 '23 at 09:52

score 2 · Answer 1 · answered Jun 20 '23 at 09:44

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

score 1 · Answer 2 · answered Jun 20 '23 at 10:32

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

score 0 · Answer 3 · edited Jun 20 '23 at 09:51

0

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:

edited Jun 20 '23 at 09:51

Dale K

25,246
15
42
71

answered Jun 20 '23 at 09:45

Mohan.V

141
1
1
10

1

To prevent XML escaping you need `FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)', 1, 2, '')` although you should really just use `STRING_AGG` – Charlieface Jun 20 '23 at 10:19

SQL concatenate row by row by date order with in group

3 Answers3