How to use group concat in a prepared MySQL statement?

Question

I have this table of product data:

+--------+--------+------------+
| item_id| amount |    date    |
+--------+--------+------------+
|      4 |    100 | 01-04-2020 |
|      6 |    200 | 01-04-2020 |
|      9 |    300 | 01-04-2020 |
|      4 |    400 | 01-04-2020 |
|      4 |    300 | 02-04-2020 |
|      6 |    150 | 02-04-2020 |
|      6 |    150 | 02-04-2020 |
|      9 |    700 | 02-04-2020 |
+--------+--------+------------+

I want to make a query which pivots this table so that the ItemId is on the column side, which displays the sum amount of all the items:

+------------+-----+-----+-----+
|    date    |  4  |  6  |  9  |
+------------+-----+-----+-----+
| 01-04-2020 | 400 | 200 | 400 |
| 02-04-2020 | 300 | 300 | 700 |
+------------+-----+-----+-----+

I was able to write a query that could do it:

SELECT 
    date,
    SUM(IF(item_id = '1', item_record.amount, NULL)) AS 1,
    SUM(IF(item_id = '2', item_record.amount, NULL)) AS 2,
    SUM(IF(item_id = '3', item_record.amount, NULL)) AS 3,
    COUNT(*) AS Total 
FROM   item_record
GROUP BY date WITH ROLLUP

But the query was hardcoded to only display the columns with item_id 1, 2, and 3. I need to make it dynamic.

Searching around StackOverflow, I encountered this question. Following the accepted answer, I tried making it into a prepared statement

SET @sql = NULL;


SELECT 
GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(item_record.item_id = "',item_record.item_id,'", item_record.amount, NULL)) 
      AS ',item_record.item_id
    )
  ) INTO @sql
FROM item_record;


SET @sql = 
CONCAT('SELECT date,    
              ',@sql,',
              COUNT(*)  AS Total 
        FROM   item_record
        GROUP BY date WITH ROLLUP
       ');

PREPARE stmt FROM @sql;
EXECUTE stmt;

But I encountered an error:

SQL Error [1064] [42000]: 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

'4,SUM(IF(item_record.item_id = "6", item_record.amount, NULL)) AS 6' at line 2

Where did I do wrong? This looks like a mere syntax error, but I've been at this for hours and I still didn't know what's up.

Seriously consider handling issues of data display in application code — Strawberry, May 06 '20 at 07:36
The built query is wrong. The result of `CONCAT` would be `SELECT date SUM(...` and is thus missing a comma. But I strongly second the comment by @Strawberry — Ulrich Thomas Gabor, May 06 '20 at 07:38
@GhostGambler I actually have a comma in the actual query. I screwed-up when copy-pasting. I've updated the question — sagungrp, May 06 '20 at 08:10
@Strawberry For the application layer, I'm using Metabase, which directly uses MySQL for data display. And don't worry, the dates are just for easy-viewing purposes. My actual query is much longer.. — sagungrp, May 06 '20 at 08:12
So, wouldn't it be simpler, smarter, more flexible, and more scalable to use Metabase for the display!?! (Caveat: I don't know metabase) — Strawberry, May 06 '20 at 08:18
Yeah, the whole point of Metabase is to display data. But it seems like my requirement is too complex for what they're capable of without descending into the level of native-query. — sagungrp, May 06 '20 at 08:26

score 1 · Answer 1 · answered May 06 '20 at 08:32

Your original query as syntax errors already. If you try to execute it, you do get the same error as with the prepared statement solution:

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 '1,
SUM(IF(item_id = '2', item_record.amount, NULL)) AS 2,
SUM(IF(item_id' at line 3

The problem is the name of the column alias in the resultset: 1 (or anything that starts with a number) is not a valid identifier - unless you surround it with backticks.

I would phrase your code as:

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
    CONCAT('SUM(CASE WHEN item_id = ', item_id, ' THEN amount END) AS `', item_id, '`')
) INTO @sql
FROM item_record;

SET @sql = CONCAT(
    'SELECT date, ', 
     @sql, 
     ', COUNT(*)  AS Total FROM item_record GROUP BY date WITH ROLLUP'
);

Note that this uses standard CASE expressions instead of IF() (both are valid in MySQL). Also, this treats item_id as a number rather than as a string - because that's what it look like.

score 1 · Answer 2 · answered May 06 '20 at 13:39

Frankly, if the app is incapable of transforming the following into something pretty, then it's probably not worth bothering with...

DROP TABLE IF EXISTS product_data;

CREATE TABLE product_data
(id SERIAL PRIMARY KEY
,item_id INT NOT NULL
,amount INT NOT NULL
,date DATE NOT NULL
);

INSERT INTO product_data VALUES
(1,4,100,'2020-04-01'),
(2,6,200,'2020-04-01'),
(3,9,300,'2020-04-01'),
(4,4,400,'2020-04-01'),
(5,4,300,'2020-04-02'),
(6,6,150,'2020-04-02'),
(7,6,150,'2020-04-02'),
(8,9,700,'2020-04-02');           


SELECT item_id
     , date
     , SUM(amount) x 
  FROM product_data 
 GROUP 
    BY date
     , item_id;
+---------+------------+------+
| item_id | date       | x    |
+---------+------------+------+
|       4 | 2020-04-01 |  500 |
|       6 | 2020-04-01 |  200 |
|       9 | 2020-04-01 |  300 |
|       4 | 2020-04-02 |  300 |
|       6 | 2020-04-02 |  300 |
|       9 | 2020-04-02 |  700 |
+---------+------------+------+

How to use group concat in a prepared MySQL statement?

2 Answers2