MySQL动态数据透视表 [英] MySQL dynamic pivot table

问题描述

我正在尝试使用具有动态列的数据透视表来工作.当 user_id 是一个字符串时，它工作正常，但如果它是一个 int，那么它似乎失败

以下是我在过去问题的帮助下到目前为止所得到的:

CREATE TABLE measure2(`inspection_date` 日期，`user_id` int，`score` int，评论文本);INSERT INTO measure2(`inspection_date`、`user_id`、`score`、评论)价值观('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;选择GROUP_CONCAT(DISTINCT)康卡特('max(user_id = '''的情况，用户身份，''' 然后计分结束) AS ',用户身份)) 进入@sql从措施2;SET @sql = CONCAT('选择检查日期，'，@sql，'从措施 2GROUP BY 检查日期');从@sql 准备 stmt;执行 stmt;解除分配准备 stmt;

参见:http://sqlfiddle.com/#!2/eab24/1

我确定它很简单，但我错过了什么?

谢谢

解决方案

由于值在 int 中，因此您将它们设为列名，因此必须将值括在反引号中

sql 将如下所示:

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

完整的查询将是:

SET @sql = NULL;选择GROUP_CONCAT(DISTINCT)康卡特('max(user_id = '''的情况，用户身份，''' 然后计分结束) AS `',用户 ID, '`')) 进入@sql从措施2;SET @sql = CONCAT('选择检查日期，'，@sql，'从措施 2GROUP BY 检查日期');从@sql 准备 stmt;执行 stmt;解除分配准备 stmt;

参见SQL Fiddle with Demo

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;

see: http://sqlfiddle.com/#!2/eab24/1

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;

See SQL Fiddle with Demo

这篇关于MySQL动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！