Mysql pivot / crosstab query

Question

Question 1: I have a table with the structure and data below:

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 entries in the following 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 that I need to use a crosstab / rotation query to get this screen. For this, I tried this based on the limited knowledge that I have. Below is my request:

 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 request, I got the following mapping:

 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 someone help me with the appropriate changes that I need to make to my request in order to get the entries in one line, and not in several lines, as mentioned above.

Question 2: There is also a way to get the value of a specific field as a column NAME. As you can see above, I have user_input1 , user_input2 , ... as the title. Instead, I want to have values ​​in customer_attribute as the column header.

To do this, I checked NAME_CONST(name,value) as shown 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 

But it gives an error

 Error Code : 1210 Incorrect arguments to NAME_CONST 

Help Wanted.

Answer 1

Although @John's static answer works fine, if you have an unknown number of columns that you want to convert, 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; 

see SQL Fiddle with Demo

As for the second, please clarify what you are trying to do, this is not clear.

Answer 2

Add GROUP_CONCAT to the CASE clause

 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

Answer 3

@DarkKnightFan, this was a very helpful question for the task I was working on. I went further and changed the solution from @bluefin to solve your second question. The following code creates your originally requested format with customer_attribute as the final column headings in the cross tab.

The corresponding change was to change:

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

For this:

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

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 viewing this issue, if you have many values ​​that you are trying to cross-talk, you may encounter an error because GROUP_CONCAT () has a maximum default length of 1024 characters. To enlarge, put this at the beginning of your prepared statement:

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