Creating a Pivot Table in MySQL When Column Values Aren't Known in Advance

Question

I have an inventory table of trousers (characterized by waist size and color) for a clothing shop which introduces four different colors each season. I need a pivot table showing the four seasonal colors from left-to-right and the waist sizes from top to bottom, and I need the query to work year after year, even as the four colors change (there will always only be four colors in the table).

The desired output would be like this

      Color1    Color2    Color3    Color4
28      nn        nn        nn        nn
30      nn        nn        nn        nn
32      nn        nn        nn        nn
34      nn        nn        nn        nn

... where "nn" is the number in inventory for the given color and waist size

Here is the setup:

CREATE TABLE Trousers ( Waist INT, Color VARCHAR(16) );
INSERT INTO Trousers VALUES ( 28, 'Grey' ), ( 28, 'Blue' ), ( 28, 'Grey' ), ( 28, 'Grey' ),
                            ( 28, 'Olive' ), ( 28, 'Olive' ), ( 28, 'Olive' ), ( 30, 'Grey' ),
                            ( 30, 'Olive' ), ( 30, 'Olive' ), ( 30, 'Grey' ), ( 30, 'Grey' ),
                            ( 32, 'Khaki' ), ( 32, 'Olive' ), ( 32, 'Grey' ), ( 32, 'Blue' ),
                            ( 32, 'Olive' ), ( 34, 'Khaki' ), ( 34, 'Blue' ), ( 34, 'Blue' );

When the colors are known in advance, the query syntax is very easy:

SELECT Waist, SUM( CASE WHEN Color='Grey'  THEN 1 ELSE 0 END ) AS Grey,
              SUM( CASE WHEN Color='Blue'  THEN 1 ELSE 0 END ) AS Blue,       
              SUM( CASE WHEN Color='Olive' THEN 1 ELSE 0 END ) AS Olive,       
              SUM( CASE WHEN Color='Khaki' THEN 1 ELSE 0 END ) AS Khaki
FROM Trousers
GROUP BY Waist
ORDER BY Waist;

As I have tried to abstract the query to get away from hard-coding the values of the colors, I have made it this far without any problems:

WITH SeasonalColors AS ( SELECT DISTINCT Color FROM Trousers ORDER BY Color )
SELECT Waist, SUM( CASE WHEN Color= (SELECT Color FROM SeasonalColors LIMIT 0,1)  THEN 1 ELSE 0 END ) AS FirstColor,
              SUM( CASE WHEN Color= (SELECT Color FROM SeasonalColors LIMIT 1,1)  THEN 1 ELSE 0 END ) AS SecondColor,       
              SUM( CASE WHEN Color= (SELECT Color FROM SeasonalColors LIMIT 2,1)  THEN 1 ELSE 0 END ) AS ThirdColor,       
              SUM( CASE WHEN Color= (SELECT Color FROM SeasonalColors LIMIT 3,1)  THEN 1 ELSE 0 END ) AS FourthColor
FROM Trousers
GROUP BY Waist
ORDER BY Waist;

However, when I try to UNION in the actual names of the colors as the first row of the output, the query fails:

WITH SeasonalColors AS ( SELECT DISTINCT Color FROM Trousers ORDER BY Color )
SELECT NULL AS Waist, (SELECT Color FROM SeasonalColors LIMIT 0,1) AS FirstColor,
             (SELECT Color FROM SeasonalColors LIMIT 1,1) AS SecondColor,
             (SELECT Color FROM SeasonalColors LIMIT 2,1) AS ThirdColor,
             (SELECT Color FROM SeasonalColors LIMIT 3,1) AS FourthColor
UNION
/* RUNNING THE QUERY FROM THE NEXT LINE DOWN WORKS FINE; INCLUDING THE PORTION ABOVE THIS LINE CAUSES AN ERROR */ 
WITH SeasonalColors AS ( SELECT DISTINCT Color FROM Trousers ORDER BY Color )
SELECT Waist, SUM( CASE WHEN Color= (SELECT Color FROM SeasonalColors LIMIT 0,1)  THEN 1 ELSE 0 END ) AS FirstColor,
              SUM( CASE WHEN Color= (SELECT Color FROM SeasonalColors LIMIT 1,1)  THEN 1 ELSE 0 END ) AS SecondColor,       
              SUM( CASE WHEN Color= (SELECT Color FROM SeasonalColors LIMIT 2,1)  THEN 1 ELSE 0 END ) AS ThirdColor,       
              SUM( CASE WHEN Color= (SELECT Color FROM SeasonalColors LIMIT 3,1)  THEN 1 ELSE 0 END ) AS FourthColor
FROM Trousers
GROUP BY Waist
ORDER BY Waist;

The error message is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH SeasonalColors AS ( SELECT DISTINCT Color FROM Trousers ORDER BY Color ) SE' at line 8

What am I doing wrong? Any help would be greatly appreciated

It's not clear what you're attempting to do with this code. Can you update your post with the expected output? — lemon, May 19 '23 at 20:47
Why do you need a header formatted inside the data? Isn't the table schema sufficient for it? — lemon, May 19 '23 at 20:53
There are two problems happening here. The first is that you can have only one `WITH` clause in a query, not one per `SELECT` that are unioned together. The second problem is that you cannot make a "dynamic" pivot query that expands automatically as it discovers new values in the future. Columns must be fixed at the time the query is parsed, so you must specify them. This means you must first do a query to discover the distinct color values, then use those to format a second query to do the pivot. This has been answered numerous times in past questions. — Bill Karwin, May 19 '23 at 21:21
@lemon, the header data is required inside the table because the values in the columns are being determined dynamically — Chris, May 19 '23 at 23:07
@BillKarwin, as stated in the question, there is no requirement for the query to expand automatically; there are always/only four columns. — Chris, May 19 '23 at 23:08
You did say you want the query to work if the colors change. So regardless of the number of columns, you want it to adapt to the distinct values in your dataset. The same principle applies — you must query the current set of distinct values, then format your pivot-table query. — Bill Karwin, May 20 '23 at 01:02
A stored proc to do the task: http://mysql.rjweb.org/doc.php/pivot — Rick James, May 20 '23 at 05:00

Creating a Pivot Table in MySQL When Column Values Aren't Known in Advance

0 Answers0