MySQL Dynamic Pivot summarised per day and sales status

Question

I have been trying to generate a MySQL Dynamic Pivot to summarise the number of sales transaction per sales status per day.

[The view I want to have must be like this:] (https://i.sstatic.net/srvIc.png)

The above image shows the number of sales transactions against each status per summarised per day.

Fir this, am using GROUP_CONCAT, however I always get an error:

SET @sql := NULL;

SELECT  GROUP_CONCAT(DISTINCT CONCAT
(
    'MAX(CASE WHEN DATE(reserved_date) = "', DATE(reserved_date), '" THEN COUNT(CASE WHEN status = "', status, '" THEN 1 END) AS ', DATE(reserved_date))
)
INTO      @sql
FROM      sales_reservation
WHERE     agent_id      NOT IN ('jorge','allan')
AND       reservation_number    != ''
ORDER BY  reserved_date;
 
SET @sql = CONCAT(
  'SELECT status, ', @sql, 'FROM sales_reservation GROUP BY status');

SELECT @sql;
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;```


This is the string that I get:
SELECT status, MAX(CASE WHEN DATE(reserved_date) = "2023-04-05" THEN COUNT(CASE WHEN status = "Awaiting Ack" THEN 1 END) AS 2023-04-05,
MAX(CASE WHEN DATE(reserved_date) = "2023-04-05" THEN COUNT(CASE WHEN status = "Reserved" THEN 1 END) AS 2023-04-05,
MAX(CASE WHEN DATE(reserved_date) = "2023-04-06" THEN COUNT(CASE WHEN status = "Awaiting Ack" THEN 1 END) AS 2023-04-06,
MAX(CASE WHEN DATE(reserved_date) = "2023-04-06" THEN COUNT(CASE WHEN status = "Reserved" THEN 1 END) AS 2023-04-06,
MAX(CASE WHEN DATE(reserved_date) = "2023-04-07" THEN COUNT(CASE WHEN status = "Awaiting Ack" THEN 1 END) AS 2023-04-07,
MAX(CASE WHEN DATE(reserved_date) = "2023-04-07" THEN COUNT(CASE WHEN status = "Awaiting Approval" THEN 1 END) AS 2023-04-07,
MAX(CASE WHEN DATE(reserved_date) = "2023-04-07" THEN COUNT(CASE WHEN status = "Reserved" THEN 1 END) AS 2023-04-07,
MAX(CASE WHEN DATE(reserved_date) = "2023-04-08" THEN COUNT(CASE WHEN status = "Reserved" THEN 1 END) AS 2023-04-08,
MAX(CASE WHEN DATE(reserved_date) = "2023-04-10" THEN COUNT(CASE WHEN status = "Awaiting Ack" THEN 1 END) AS 2023-04-10,

Can someone help me out? I am using mysql and there is no pivot like MS SQL

Answer 1

I suggest the following (revised) query, but before using it consider this "fiddle" https://dbfiddle.uk/Dzziu8f1 where you can see the query in operation:

SET @sql := NULL;

SELECT  GROUP_CONCAT(DISTINCT CONCAT(
    'SUM(CASE WHEN DATE(reserved_date) = "', DATE(reserved_date), '" THEN 1 ELSE 0 END) AS `', DATE_FORMAT(reserved_date, '%d/%m/%Y'), '`'
))
INTO      @sql
FROM      sales_reservation
WHERE     agent_id      NOT IN ('jorge','allan')
AND       reservation_number    != ''
ORDER BY  reserved_date;
 
SET @sql = CONCAT(
  'SELECT status, ', @sql, ' FROM sales_reservation GROUP BY status');

SELECT @sql;
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

example output:

status	01/01/2022	02/01/2022
confirmed	1	0
pending	0	1
cancelled	0	0

NOTE you probably need to restrict how many days will be reported otherwise performance may be poor.