mysql 数据透视表/交叉表查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12382771/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql pivot/crosstab query
提问by DarkKnightFan
Question 1:I have a table with the below structure and data:
问题 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
I want to display these records in the below format:
我想以以下格式显示这些记录：
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 |
I know I need to use crosstab/pivot query to get this display. For this I tried it based on the limited knowledge that I have about it. Following is my query:
我知道我需要使用交叉表/枢轴查询来获得此显示。为此，我根据我对它的有限知识进行了尝试。以下是我的查询：
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
And based on this query I got the below display:
基于这个查询，我得到了以下显示：
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
Can anyone help me with the proper changes that I need to make to my query to get the records in one single row and not multiple rows as above.
任何人都可以帮助我进行适当的更改，我需要对我的查询进行适当的更改，以获取单行中的记录，而不是如上所述的多行。
Question 2:Also is there a way to get the value of a particular field as the NAME of the column. As you can see above I have user_input1, user_input2,... as the header. Instead of that I want to have the values in customer_attributeas the header of the columns.
问题 2：还有一种方法可以获取特定字段的值作为列的名称。正如你在上面看到的，我有user_input1, user_input2,... 作为标题。相反，我希望将值customer_attribute作为列的标题。
For this I checked NAME_CONST(name,value)as below:
为此，我检查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
But it gives an error
但它给出了一个错误
Error Code : 1210 Incorrect arguments to NAME_CONST
Help required.
需要帮助。
回答by Taryn
While @John's static answer works great, if you have an unknown number of columns that you want to transform, I would consider using prepared statements to get the results:
虽然@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;
As far as your second, please clarify what you are trying to do it is not clear.
至于您的第二个，请澄清您正在尝试做的事情尚不清楚。
回答by John Woo
Add GROUP_CONCATin your CASEclause
添加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 Demo
SQLFiddle 演示
回答by Kolath
@DarkKnightFan, this was a very helpful question for a task I was working. I went ahead and modified the solution from @bluefin to solve your second question. The following code produces your originally requested format with the value of customer_attribute as the resulting column headings in the cross-tab.
@DarkKnightFan，对于我正在处理的任务来说，这是一个非常有用的问题。我继续修改@bluefin 的解决方案以解决您的第二个问题。以下代码生成您最初请求的格式，并将 customer_attribute 的值作为交叉表中的结果列标题。
The relevant change was to change:
相关的变化是改变：
' then entered_value else NULL END)) AS user_input',
node_id
To this:
对此：
' then entered_value else NULL END)) AS ''',
customer_attribute,''''
The full code:
完整代码：
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;
Also, for other users browsing this issue, if you have a lot of values that you are trying to cross-tab, you may run into error because GROUP_CONCAT() has a default max length of 1024 characters. To increase put this at the start of your prepared statement:
此外，对于浏览此问题的其他用户，如果您尝试交叉表的值很多，您可能会遇到错误，因为 GROUP_CONCAT() 的默认最大长度为 1024 个字符。为了增加把它放在你准备好的语句的开头：
SET SESSION group_concat_max_len = value; -- replace value with an int
