MySQL Rows to Columns – How to Change Rows to Columns

MySQL

I have data as following(it is just an sample, I have 100+ different types.):

advertiser_id activity_type counts
1               1            2
1               2            5
2               1            3
2               3            9

I would like to change it into 

advertiser_id activity_type1 activity_type2 activity_type3 
1              2                 5           0
2              3                 0           9

I wrote code as 

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(`activity_type` = ', activity_type, ',counts,NULL)) AS counts', activity_type)) INTO @sql FROM ceciliadb.tbltmp_act
;

SET @sql = CONCAT('SELECT advertiser_id, ', @sql, 'FROM ceciliadb.tbltmp_act GROUP BY advertiser_id');

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

But there is error as 

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ceciliadb.tbltmp_act GROUP BY advertiser_id' at line 1

Is there anyone can help?

Best Answer

As for your original question, you need an extra space before FROM in the line before your PREPARE

SET @sql = CONCAT('SELECT advertiser_id, ', @sql, ' FROM ceciliadb.tbltmp_act GROUP BY advertiser_id');

SUGGESTION

At first glance, the query you need generated looks like this

SELECT
    advertiser_id,
    IF(activity_type=1,counts,0) activity_type1,
    IF(activity_type=2,counts,0) activity_type2,
    IF(activity_type=3,counts,0) activity_type3
FROM ceciliadb.tbl.tmp_act
GROUP BY advertiser_id;

I would craft it like this:

SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Since you have 100 types, you nned to wedge those 100 type IDs in the subquery

SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Since the generated query may be too long, set the GROUP_CONCAT length to 10 MB beforehand

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

If your columns are not sequential in nature, then use the actual values as headers

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT DISTINCT activity_type FROM ceciliadb.tbl.tmp_act) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;
Related Question