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.
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.
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
But it gives an error
Error Code : 1210 Incorrect arguments to NAME_CONST
Help required.
Moreover, we can create different pivot tables based on the same raw data by using the crosstab function. Try building a pivot table that shows the max temperature for each city and month based on the raw data in the table below. The pivot table should have one row for each city and one column for each month.
With a basic crosstab, you would have to go back to the program and create a separate crosstab with the information on individual products. Pivot tables let the user filter through their data, add or remove custom fields, and change the appearance of their report.
If you already know which columns to create in pivot table, you can use a CASE statement to create a pivot table. However, to create dynamic pivot tables in MySQL, we use GROUP_CONCAT function to dynamically transpose rows to columns, as shown below.
A database table can store different types of data and sometimes we need to transform row-level data into column-level data. This problem can be solved by using the PIVOT() function. This function is used to rotate rows of a table into column values.
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;
see SQL Fiddle with Demo
As far as your second, please clarify what you are trying to do it is not clear.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With