Petay87 Petay87 - 1 month ago 11
MySQL Question

Limit the columns displayed in a MYSQL pivot table

The Question:



How do I limit the number of columns displayed/produced on a MYSQL pivot table?

My Setup:



I have a table named "updates" that looks like the following:

updates table

I have the following snippet of query (This is only part of the query, the whole thing only adds more columns from other tables but this is the only section that gets pivoted):

SET @sql = NULL;

SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(Date = ''',
Date,
''', Description, NULL)) AS ',
CONCAT("'",Date_Format(Date, '%d/%m/%Y'),"'")
)
)INTO @sql
FROM updates;

SET @sql = CONCAT('SELECT Action, ', @sql, ' FROM updates GROUP BY Action');

PREPARE stmt FROM @sql;
EXECUTE stmt;


The result of this query is as follows:

enter image description here

As you can see, this pivots the table as intended with the dates as columns. However, there is potential for these updates (to actions) to become very long before they are "closed" and not displayed. Therefore, I would like to limit the outcome to the latest 3 updates. BUT..Not per action as this would potentially still give me a lot of updates in the pivot table.

I would like to have the most recent 3 dates from the updates table with all updates for each date keeping this pivot format.

Example: The outcome table above would look the same but with the exception of the columns titled "02/10/2016" and "04/10/2016".

Thanks in advance for any assistance or advise.

Answer

For anyone else trying to solve this issue, I managed to use the following query to produce the desired results:

SET @sql = NULL;

SELECT
   GROUP_CONCAT(DISTINCT
     CONCAT(
       'MAX(IF(Date = ''',
       Date,
       ''', Description, NULL)) AS ',
       CONCAT("'",Date_Format(Date, '%d/%m/%Y'),"'")
     ) ORDER BY Date ASC 
   ) INTO @sql
FROM (
    SELECT * FROM updates
    GROUP BY Date
    ORDER BY Date DESC
    LIMIT 2)
    AS updates;

SET @sql = CONCAT('SELECT Action, ', @sql, ' FROM updates GROUP BY Action');

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