MySQL数据透视/交叉表查询

Question

问题1：我有一个具有以下结构和数据的表：app_id  transaction_id  mobile_no   node_id  customer_attribute  entered_value 100     111             9999999999  1        Q1                  2                             100     111             9999999999  2        Q2                  1                             100     111             9999999999  3        Q3                  4                             100     111             9999999999  4        Q4                  3                             100     111             9999999999  5        Q5                  2                             100     222             8888888888  4        Q4                  1                             100     222             8888888888  3        Q3                  2                             100     222             8888888888  2        Q2                  1                             100     222             8888888888  1        Q1                  3                             100     222             8888888888  5        Q5                  4                             我想以以下格式显示这些记录：app_id  |  transaction_id  | mobile     |  Q1  |  Q2  |  Q3  |  Q4 |  Q5  | 100    |      111         | 9999999999 |   2  |   1  |   4  |  3  |  2   | 100    |      222         | 8888888888 |   3  |   1  |   2  |  1  |  4   |我知道我需要使用交叉表/数据透视查询来获得此显示。为此，我基于对它的有限知识对其进行了尝试。任何人都可以帮助我对查询进行适当的更改以获取单行而不是如上所述的多行记录。问题2：还有一种方法可以获取特定字段的值作为列的名称。正如你可以在上面看到我有user_input1，user_input2，...作为标题。取而代之的是，我想将值customer_attribute作为列的标题。为此，我检查NAME_CONST(name,value)如下：SELECT app_id, transaction_id, mobile_no,NAME_CONST(customer_attribute, (CASE node_id WHEN 1 THEN entered_value ELSE '' END))FROM trn_user_log 但它给出了一个错误Error Code : 1210 Incorrect arguments to NAME_CONST需要帮助。

Answer 1

虽然@John的静态答案很好用，但是如果您要转换的列数未知，我会考虑使用准备好的语句来获取结果：

SET @sql = NULL;

SELECT

  GROUP_CONCAT(DISTINCT

    CONCAT(

      'GROUP_CONCAT((CASE node_id when ',

      node_id,

      ' then entered_value else NULL END)) AS user_input',

      node_id

    )

  ) INTO @sql

FROM trn_user_log;

SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, ' 

                  FROM trn_user_log 

                  GROUP BY app_id, transaction_id, mobile_no');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

参见带有演示的SQL Fiddle

至于您的第二个，请说清楚您要做什么。

Answer 2

对于我正在执行的任务，这是一个非常有用的问题。我继续修改了@bluefin的解决方案，以解决您的第二个问题。以下代码生成您最初请求的格式，其值Customer_attribute作为交叉表中的结果列标题。

相关的更改是更改：

' then entered_value else NULL END)) AS user_input',

      node_id

对此：

' then entered_value else NULL END)) AS ''',

          customer_attribute,''''

完整代码：

SET @sql = NULL;

SELECT

  GROUP_CONCAT(DISTINCT

    CONCAT(

      'GROUP_CONCAT((CASE node_id when ',

      node_id,

      ' then entered_value else NULL END)) AS ''',

      customer_attribute,''''

    )

  ) INTO @sql

FROM trn_user_log;

SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, ' 

                  FROM trn_user_log 

                  GROUP BY app_id, transaction_id, mobile_no');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

另外，对于浏览此问题的其他用户，如果您有很多试图交叉制表的值，则可能会出错，因为GROUP_CONCAT（）的默认最大长度为1024个字符。为了增加这一点，请将其放在准备好的语句的开头：

SET SESSION group_concat_max_len = value; -- replace value with an int