MySQL Group By values equal values over multiple columns, output lookup value as field name, group by date

Question

Similar to MySQL Group By values that are equal over multiple columns

How do I transform the mysql database table below to the output using MySQL:

id entity_id volunteer_1 volunteer_2 volunteer_3
 1      1540        1933        1253        2543
 2      1638        2543        2123        1736
 3      1736        1253        1933        2123
 4      1834        1525        1253        2123
 5      1932        2123        2543        2451
 6      2030        2854        2451        1933
 7      2128        2451        2854        2543

To the output

              Jan-15 Feb-16 Mar-16
Actual Name 1      3      4      6
Actual Name 2      3      3      5
Actual Name 3      1      5      6

Things to note:

Date time is determined by entity_id field linking to an activity.activity_date_time
Each volunteer ID should output actual name by linking to contact.first_name and contact.last_name of the ID in the field

`Date time is determined by entity_id field linking to an activity.activity_date_time` Is that some kind of invisible table? — Strawberry, Aug 31 '16 at 13:27
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Aug 31 '16 at 13:28
Not invisible. Same DB. The 4 digit numbers themselves are foreign keys (contact.id) to the contact table. — adamk, Aug 31 '16 at 13:28
You want to create a pivot table as a result. Depending on whether your date range is fixed or dynamic, you need static or dynamic pivoting. The accepted answer in the linked duplicate question provides an example for both scenarios. — Shadow, Aug 31 '16 at 13:37
Date range is dynamic per entity_id. i.e. activity.activity_date_time could be any date/time field. Require these grouped by month. — adamk, Aug 31 '16 at 13:40

adamk · Answer 1 · 2016-09-01T08:46:31.787

Solved by doing a union first, to get the data per the suggested answer (single column format). The suggested answer doesn't consolidate similar data from multiple columns. Then you can work with the temporary results and transform as required. Further, it has to be a stored procedure, to the statement won't execute with results.

    DELIMITER $$
    DROP PROCEDURE IF EXISTS `civicrm_report_tmp_volunteer`$$

    CREATE PROCEDURE `civicrm_report_tmp_volunteer`()
    BEGIN
            DROP TABLE IF EXISTS civicrm_report_tmp_volunteer_hours;
            SET @group_concat_max_len = 5000;


            CREATE TEMPORARY TABLE civicrm_report_tmp_volunteer_hours AS 
            SELECT
                CONCAT(c.first_name, ' ' , c.last_name) as contact_name,
                a.contact_id as contact_id,
                EXTRACT( YEAR_MONTH FROM ca.activity_date_time ) as sort_date,
              CONCAT(
                    MONTHNAME(STR_TO_DATE(EXTRACT( MONTH FROM ca.activity_date_time), '%m')),
                    "-",
                    EXTRACT( YEAR FROM ca.activity_date_time )) as month_year,
                -- a.activity_id,
                -- ca.activity_type_id,
                -- ca.activity_date_time,
                -- ca.duration,
                SUM(ca.duration) as activity_duration,
                COUNT(DISTINCT (a.activity_id)) activity_count
            FROM
            (
              SELECT volunteer_1_543 as contact_id, entity_id as activity_id FROM civicrm_value_volunteer_details_103
              WHERE volunteer_1_543 IS NOT NULL
              UNION
              SELECT volunteer_2_544 as contact_id, entity_id as activity_id  FROM civicrm_value_volunteer_details_103
              WHERE volunteer_2_544 IS NOT NULL
              UNION
              SELECT volunteer_3_545 as contact_id, entity_id as activity_id  FROM civicrm_value_volunteer_details_103
              WHERE volunteer_3_545 IS NOT NULL
              UNION
              SELECT volunteer_4_546 as contact_id, entity_id as activity_id  FROM civicrm_value_volunteer_details_103
              WHERE volunteer_4_546 IS NOT NULL
            ) as a
            LEFT JOIN civicrm_activity ca
            ON a.activity_id = ca.id
            LEFT JOIN civicrm_contact c
            ON c.id = a.contact_id
            WHERE ca.activity_type_id IN (184)
            GROUP BY a.contact_id, month_year;

            SET @query = null;

            SELECT
              GROUP_CONCAT(DISTINCT
                CONCAT(
                  'MAX(IF(sort_date = ''',
                  sort_date,
                  ''', activity_duration, 0)) AS ',
                  "'", month_year, "' "
                )
              ) INTO @query
            FROM civicrm_report_tmp_volunteer_hours;

            SET @query = CONCAT('SELECT contact_name, ', @query , ' FROM civicrm_report_tmp_volunteer_hours GROUP BY contact_id');
            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
    END$$

    DELIMITER ;

    CALL civicrm_report_tmp_volunteer();

MySQL Group By values equal values over multiple columns, output lookup value as field name, group by date

1 Answers1