mysql如何实现透视表功能,MySQL动态数据透视表

Im trying to get a pivot table with dynamic columns to work. When user_id is a string, it works fine but if its an int, then it seems to fail

Here is what I have so far with the assistance of past questions:

CREATE TABLE measure2

(`inspection_date` date, `user_id` int, `score` int, comment text)

;

INSERT INTO measure2

(`inspection_date`, `user_id`, `score`, comment)

VALUES

('2012-10-16', 0, 0, null),

('2012-10-16', 1, 0, null),

('2012-10-16', 2, 0, null),

('2012-10-16', 3, 0, null),

('2012-10-17', 0, 1, null),

('2012-10-17', 1, 1, null),

('2012-10-17', 2, 1, null),

('2012-10-18', 3, 1, null)

;

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'max(case when user_id = ''',

user_id,

''' then score end) AS ',

user_id

)

) INTO @sql

FROM measure2;

SET @sql = CONCAT('SELECT inspection_date, ', @sql, '

FROM measure2

GROUP BY inspection_date');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Im sure its something simple, but what am I missing?

Thanks

解决方案

Since the values are in int you are are making them the column names, you have to wrap the values in a backtick

The sql will look like:

max(case when user_id = 1 then score end) as `1`

The full query will be:

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'max(case when user_id = ''',

user_id,

''' then score end) AS `',

user_id, '`'

)

) INTO @sql

FROM measure2;

SET @sql = CONCAT('SELECT inspection_date, ', @sql, '

FROM measure2

GROUP BY inspection_date');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;