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, How do I create a comma-separated list using a SQL query?, Multiple rows to one comma-separated value in Sql Server [duplicate], SQL convert column to comma separated row, etc, etc, etc. — Thom A
– Thom A ♦, Commented Jun 20, 2023 at 9:06
There are literally 100's of duplicates for this; take your pick. — Thom A
– Thom A ♦, Commented Jun 20, 2023 at 9: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
– Charlieface, Commented Jun 20, 2023 at 9:36
Fyi please don't use images for data - use formatted tabular text — Dale K
– Dale K, Commented Jun 20, 2023 at 9:52

Zhorov · Accepted Answer · 2023-06-20 09:44:37Z

2

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

answered Jun 20, 2023 at 9:44

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2023-06-20 10:32:55Z

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

Dale K · Accepted Answer · 2023-06-20 09:51:28Z

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, 2023 at 9:51

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 20, 2023 at 9:45

Mohan.V

1411 gold badge1 silver badge13 bronze badges

1 Comment

Charlieface Over a year ago

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

Collectives™ on Stack Overflow

SQL concatenate row by row by date order with in group

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related