MySQL数据透视表/交叉表查询 [英] mysql pivot/crosstab query
问题描述
问题1:我有一个具有以下结构和数据的表:
Question 1: I have a table with the below structure and data:
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.
问题2:还有一种获取特定字段的值作为列名的方法.如您在上面看到的，我有user_input1，user_input2，...作为标题.取而代之的是，我想将customer_attribute中的值用作列的标题.
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_attribute as the header of the columns.
为此，我检查了NAME_CONST(name,value)，如下所示:
For this I checked NAME_CONST(name,value) as below:
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
需要帮助.
推荐答案
虽然@John的静态答案非常有用，但是如果要转换的列数未知，我会考虑使用准备好的语句来获取结果:
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:
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小提琴
就您的第二个而言，请弄清楚您要做什么，尚不清楚.
As far as your second, please clarify what you are trying to do it is not clear.
这篇关于MySQL数据透视表/交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
