Question 209765 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Pivot Using Date As Value

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

ID Visit Date
1234567 Cake 01.01.2023
1234567 Coffee 01.01.2023
1234567 Cake 02.01.2023
2345678 Coffee 02.02.2023
2345678 Coffee 03.02.2023

to:

ID Cake Coffee
1234567 Max(Date) 02.01.2023 Max(Date) 01.01.2023
2345678 Max(Date) None Max(Date) 03.02.2023

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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 IDnt,', 
       GROUP_CONCAT(DISTINCT CONCAT(
       'MAX(CASE WHEN Visit= ''', Visit, ''' THEN `Date` END) AS ', 
       Visit, ' ') SEPARATOR  'nt,'),  
      '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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search