MySql query to dynamically convert rows to columns with grouping by DATA_FORMAT

Question

I have two tables in my MySQL 5.5 database:

CREATE TABLE `t_user` (
  `USER_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `t_user_activity_log` (
  `USER_ID` int(10) unsigned NOT NULL,
  `ACTIVITY_DATE` datetime NOT NULL,
  `ACTIVITY_COUNT` int(10) unsigned NOT NULL,
  PRIMARY KEY (`USER_ID`,`ACTIVITY_DATE`),
  CONSTRAINT `FK_t_user` FOREIGN KEY (`USER_ID`) REFERENCES `t_user` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And I need to get result grouped by time period like:

----------------------------------------------
| Period     | User1 | User2 | ***** | UserN |
----------------------------------------------
| 22.02.2012 |   12  |   12  |   x   |   x   |
----------------------------------------------
| 23.02.2012 |   7   |   3   |   x   |   x   |
----------------------------------------------
| 24.02.2012 |   0   |   0   |   0   |   0   |
----------------------------------------------

Period is changeable (HOUR, WEEK, MONTH, YEAR). It should be possible to limit query by FROM and TO dates and if no records for date is found - it should still be visible in result. Users should be selected from the list in().

Is it possible at all?

score 0 · Answer 1 · answered Aug 17 '15 at 16:47

You can do something like that, obviously you would generate this query with your server language from the list of your actual users.

SELECT 
t.ACTIVITY_DATE,
SUM(IF(t.USER_ID = 1, t.activity_count, 0)) as 'user1',
SUM(IF(t.USER_ID = 2, t.activity_count, 0)) as 'user2'

FROM (
SELECT t_user.USER_ID, 
    t_user.USER_NAME, 
    t_user_activity_log.ACTIVITY_DATE, 
    SUM(t_user_activity_log.ACTIVITY_COUNT) as activity_count
FROM t_user_activity_log 
INNER JOIN t_user ON t_user_activity_log.USER_ID = t_user.USER_ID

GROUP BY t_user.USER_ID, t_user_activity_log.ACTIVITY_DATE
) as t

GROUP BY t.USER_ID, t.ACTIVITY_DATE

score 0 · Answer 2 · answered Aug 17 '15 at 16:55

If you want to keep it in the database, you can create a dynamic query inside a procedure like this

    DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamic_query`$$
CREATE PROCEDURE `dynamic_query`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_name VARCHAR(255);

    #Cursor to fetch all the users
    DECLARE cursor_user CURSOR FOR SELECT t_user.USER_ID, t_user.USER_NAME FROM t_user;


    DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET done = 1;

    # start of the dynamic query
    SET @query = CONCAT('SELECT t.ACTIVITY_DATE\n');

    OPEN cursor_user;
    # looping over the cursor
    user_loop: LOOP
        FETCH cursor_user INTO user_id, user_name;
        # Leaving the loop inf no more records found
        IF done = 1 THEN
            LEAVE user_loop;
        END IF;
        #Concatenate the users in the dynamic query
        SET @query = CONCAT(@query,',SUM(IF(t.USER_ID = ',user_id,', t.activity_count, 0)) as \'',user_name,'\'');
    END LOOP;

    CLOSE cursor_user;

    #the rest of the dynamic query
    SET @query = CONCAT(@query,' FROM (SELECT t_user.USER_ID, t_user.USER_NAME, t_user_activity_log.ACTIVITY_DATE, SUM(t_user_activity_log.ACTIVITY_COUNT) as activity_count FROM t_user_activity_log INNER JOIN t_user ON t_user_activity_log.USER_ID = t_user.USER_ID GROUP BY t_user.USER_ID, t_user_activity_log.ACTIVITY_DATE) as t GROUP BY t.USER_ID, t.ACTIVITY_DATE');

    # Preparing and executing the dynamic query
    PREPARE stmt FROM @query;
    EXECUTE stmt;

    # Cleaning up
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

MySql query to dynamically convert rows to columns with grouping by DATA_FORMAT

2 Answers2