MySQL Pivot Using Date As Value

I try to create a procedure that transforms the following table from:

to:

The number of different items in column Visit (and all other columns) can change dynamically.

I noticed that mysql does not support pivot, and based on what I found on the internet, I tried the following.

Unfortunately, I don't even get this code to run. Do you have any ideas on what I can improve?

Thank you for your support!!! Thats so much appreciated!!

Best,

Janine

Code:

CREATE PROCEDURE VisitReport.Pivot()
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
  'MAX(CASE WHEN Visit= "', Visit, '" THEN [Date] ELSE 0 END) 
  AS ', Visit, '"')
)
INTO @sql
FROM VisitReport;
 
SET @sql = CONCAT('SELECT ID, ', @sql, 
  ' FROM VisitReport GROUP BY ID');

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

I have modified the sample from Joel Coehoorn, with prepare and execute. So its nothing to copy

see here:

https://dbfiddle.uk/KE6-DbTV

You can try the following:

DELIMITER //
CREATE PROCEDURE GetCakeCoffeeDates()
BEGIN
    SELECT 
        id, 
        (CASE WHEN cakeDate IS NULL THEN 'None' ELSE cakeDate END) AS cake,
        (CASE WHEN coffeeDate IS NULL THEN 'None' ELSE coffeeDate END) AS coffee
    FROM 
        (SELECT 
            c.`id`, 
            MAX(CASE WHEN c.`visit`='Cake' THEN c.`date` END) AS cakeDate,
            MAX(CASE WHEN c.`visit`='Coffee' THEN c.`date` END) AS coffeeDate
        FROM `cake_coffee` c GROUP BY c.`id`) a;
END //
DELIMITER ;

to call the procedure:

CALL GetCakeCoffeeDates;

I am just assuming that there will be none other than cake and coffee.

Let me know if I am missing something

> The number of different items in column Visit (and all other columns) can change dynamically.

Given that information, this is impossible to do in a single SQL statement.

The SQL language has a very strict rule that the number and types of columns must be known at query compile time, before looking at any data. Since you have to look at the data to know what columns to show, you will not be able to do this in a single SQL query.

Instead, you must use dynamic SQL do this over three steps:

1. Run a query to determine what columns you will need.
2. Use the results from step 1 to build a new SQL statement that explicitly lists each of the columns you will need
3. Run the query from step 2.

It's also worth noting that adding PIVOT support would not help. Even with PIVOT, you still need to know that number of result columns.

That said, you'd made good progress towards this result. Once we know the columns, PIVOT might improve this query, but without it the conditional aggregation strategy already attempted is our best bet. I only needed to clean up a few syntax issues with using single quotes instead of double and back-ticks instead of square-brackets. I took the liberty of improving the format of the result as well, which helps with debugging:

SELECT
    CONCAT('SELECT ID\n\t,', 
       GROUP_CONCAT(DISTINCT CONCAT(
       'MAX(CASE WHEN Visit= ''', Visit, ''' THEN `Date` END) AS ', 
       Visit, ' ') SEPARATOR  '\n\t,'),  
      '\nFROM VisitReport GROUP BY ID;') AS q
FROM VisitReport;

See it here:

> https://dbfiddle.uk/7YmQeAMf

The fiddle just copy/pastes the result from the 2nd sample block to the third to give the correct result.