MySQL的枢轴/交叉表查询(mysql pivot/crosstab query)

问题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   |

我知道我需要使用交叉表/透视查询来获取此显示。 为此，我试过它的基础上，我有关于它的知识有限。 以下是我的查询：

SELECT app_id, transaction_id, mobile_no,
  (CASE node_id WHEN 1 THEN entered_value ELSE '' END) AS user_input1,
  (CASE node_id WHEN 2 THEN entered_value ELSE '' END) AS user_input2,
  (CASE node_id WHEN 3 THEN entered_value ELSE '' END) AS user_input3,
  (CASE node_id WHEN 4 THEN entered_value ELSE '' END) AS user_input4,
  (CASE node_id WHEN 5 THEN entered_value ELSE '' END) AS user_input5
FROM trn_user_log 
GROUP BY app_id, transaction_id, mobile_no, node_id

在此基础上查询，我得到了如下显示：

app_id  transaction_id  mobile_no   user_input1  user_input2  user_input3  user_input4  user_input5  
100     111             9999999999  2                                                                
100     111             9999999999               1                                                   
100     111             9999999999                            4                                      
100     111             9999999999                                         3                         
100     111             9999999999                                                      2            
100     222             8888888888  3                                                                
100     222             8888888888               1                                                   
100     222             8888888888                            2                                      
100     222             8888888888                                         1                         
100     222             8888888888                                                      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:

虽然@约翰的静态回答的伟大工程，如果你有一个未知的数量要改造，我会考虑使用准备好的发言得到的结果列：

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拨弄演示

至于你的第二个，请说明你正在尝试做的，目前尚不清楚。

Answer 2:

添加GROUP_CONCAT你的CASE子句

SELECT app_id, transaction_id, mobile_no,
  GROUP_CONCAT((CASE node_id WHEN 1 THEN entered_value ELSE NULL END)) AS user_input1,
  GROUP_CONCAT((CASE node_id WHEN 2 THEN entered_value ELSE NULL END)) AS user_input2,
  GROUP_CONCAT((CASE node_id WHEN 3 THEN entered_value ELSE NULL END)) AS user_input3,
  GROUP_CONCAT((CASE node_id WHEN 4 THEN entered_value ELSE NULL END)) AS user_input4,
  GROUP_CONCAT((CASE node_id WHEN 5 THEN entered_value ELSE NULL END)) AS user_input5
FROM trn_user_log 
GROUP BY app_id, transaction_id, mobile_no

SQLFiddle演示

Answer 3:

@DarkKnightFan，这对我的工作任务非常有帮助的问题。 我继续从@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

文章来源: mysql pivot/crosstab query