SQL JOIN while concatenating data from subsequent rows

Question

I'm having a hard time trying to puzzle together a query against an older MySQL 5.0 DB. The data model isn't optimal to begin with.

Data

ReceiptID	LineNbr	ProdNbr	Description	PrincipleDescription
54092	1	1023	abc	abc
54092	2	731	def	def
54092	3	617	ghi	ghi
54092	4	618	rst	ghi
54092	5	692	xyz	ghi
54092	6	331	jkl	jkl
54092	7	608	mno	mno
54092	8	618	rst	mno
54092	9	1040	ahd	ahd

The Description values rst, xyz are prefixed with two space characters thus stored in the DB as e.g. ' rst'. These are added comments/notes to the preceding line item that ain't prefix with spaces.

Expected Output:

ReceiptID	LineNbr	ProdNbr	DESCRIPTION	PrincipleDescription
54092	1	1023	abc	abc
54092	2	731	def	def
54092	3	617	ghi	ghi (rst, xyz)
54092	6	331	jkl	jkl
54092	7	608	mno	mno (rst)
54092	9	1040	ahd	ahd

I was thinking LEAD() or alike could help out here but that only got introduced since MySQL 8 thus that ain't available for my use case. Any other ways I could achieve such result instead?

Answer 1

In MySQL 5, you can use variables to store values of your fields, then change variables values any time there's a change in either "ReceiptID" or "PrincipleDescription" fields. Once you have selected your first values fields for both "LineNbr" and "ProdNbr", you can apply aggregation.

SET @receiptID   = '';
SET @lineNbr     = -1; 
SET @prodNbr     = -1;
SET @descr       = '';

SELECT ReceiptID,
       LineNbr,
       ProdNbr, 
       GROUP_CONCAT(Description) AS DESCRIPTION,
       PrincipleDescription
FROM (SELECT IF(ReceiptID = @receiptID AND PrincipleDescription = @descr, 
                @lineNbr, @lineNbr := LineNbr         ) AS LineNbr,
             IF(ReceiptID = @receiptID AND PrincipleDescription = @descr, 
                @prodNbr, @prodNbr := ProdNbr         ) AS ProdNbr,
             Description,
             IF(ReceiptID = @receiptID AND @descr = PrincipleDescription,
                @descr, @descr := PrincipleDescription) AS PrincipleDescription,
             IF(ReceiptID = @receiptID, 
                @receiptID, @receiptID := ReceiptID   ) AS ReceiptID
      FROM tab) cte
GROUP BY ReceiptID,
         LineNbr,
         ProdNbr, 
         PrincipleDescription

Check the demo here.

---

In MySQL 8, you can first select the needed values with the FIRST_VALUE window function, then aggregate the "Description" on the other fields:

WITH cte AS (
    SELECT ReceiptID, 
           FIRST_VALUE(LineNbr) OVER(
               PARTITION BY ReceiptID, PrincipleDescription
               ORDER     BY LineNbr                         ) AS LineNbr,
           FIRST_VALUE(ProdNbr) OVER(
               PARTITION BY ReceiptID, PrincipleDescription
               ORDER     BY LineNbr                         ) AS ProdNbr,
           FIRST_VALUE(Description) OVER(
               PARTITION BY ReceiptID, PrincipleDescription
               ORDER     BY LineNbr                         ) AS Description,
           Description                                        AS PrincipleDescription
    FROM tab
)
SELECT ReceiptID, LineNbr, ProdNbr, Description,
       GROUP_CONCAT(PrincipleDescription) AS PrincipleDescription 
FROM cte
GROUP BY ReceiptID, LineNbr, ProdNbr, Description

Check the demo here.