Grouping results belonging to the same product number into the same record

Question

I have a very long query that I have shortened and anonymized so the problem becomes clearer.

Here is an approximation of my query:

SELECT subquery1.productNumber,
       subquery1.anotherinfo,
       subquery2.label_a,
       subquery2.label_b,
       subquery2.label_c
FROM (SELECT productNumber,
             anotherinfo
      FROM table1
      WHERE somethingelse = 'limitation') as subquery1
LEFT JOIN (SELECT productNumber,
                  CASE WHEN table2.label = 'H' THEN 'example1'
                       WHEN table2.label = 'I' THEN 'example2' END AS 'label_a',
                  CASE WHEN table2.label = 'J' THEN 'example3'
                       WHEN table2.label = 'K' THEN 'example4' 
                       WHEN table2.label = 'L' THEN 'example5' END AS 'label_b',
                  CASE WHEN table3.label = 'M' THEN 'example6' END AS 'label_c'
           FROM table2
           WHERE label IN ('H', 'I', 'J', 'K', 'L', 'M')) AS subquery2 
       ON subquery2.productNumber = subquery1.productNumber
ORDER BY subquery1.productNumber ASC

"Current Output":

ProductNumber	anotherInfo	label_a	label_b	label_c
10001	(correct result)	example1
10001	(corrrect result)		example4
10001	(correct result)			example6
10002	(correct result)	example2
10002	(correct result)		example5
10002	(correct result)			example6
10003	(correct result)	example1
10003	(correct result)		example5

I'd want to have one row for each "ProductNumber" with the information for the labels in the same record. I cannot use GROUP BY instead of ORDER BY because, as soon as I use it, label_b gets recorded earliest in table2, so that information is the only one displayed from that subquery, and the result changes to the following:

ProductNumber	anotherInfo	label_a	label_b	label_c
10001	(corrrect result)		example4
10002	(correct result)		example5
10003	(correct result)		example5

More subqueries are not a preferred option as the entire thing is already way too big. It should be possible to fuse it via Spreadsheet/Excel, but maybe there is a simple Mysql solution for it I just have not thought about yet.

Does anyone have an idea on how to solve it?

Answer 1

You need to apply aggregation in the subquery to have a full pivot:

• MAX aggregate function will allow you to get the only non-null value
• "productNumber" in the GROUP BY clause will allow you to do it for each distinct productNumber.

SELECT s1.productNumber,
       s1.anotherinfo,
       s1.label_a,
       s1.label_b,
       s1.label_c
FROM      (SELECT productNumber, anotherinfo
           FROM table1
           WHERE somethingelse = 'limitation') as s1
LEFT JOIN (SELECT productNumber,
                  MAX(CASE WHEN table2.label = 'H' THEN 'example1'
                           WHEN table2.label = 'I' THEN 'example2' 
                      END) AS 'label_a',
                  MAX(CASE WHEN table2.label = 'J' THEN 'example3'
                           WHEN table2.label = 'K' THEN 'example4' 
                           WHEN table2.label = 'L' THEN 'example5' 
                      END) AS 'label_b',
                  MAX(CASE WHEN table3.label = 'M' THEN 'example6' 
                      END) AS 'label_c'
           FROM table2
           WHERE label IN ('H', 'I', 'J', 'K', 'L', 'M')
           GROUP BY productNumber) AS s2
       ON s2.productNumber = s1.productNumber
ORDER BY s1.productNumber asc