How to Create a MySQL Crosstab Query to Pivot Data from Rows to Columns?

MySQL Pivot/Crosstab Query: Transforming Data for Enhanced Display

A crosstab or pivot query in MySQL allows you to transform data, typically from a row-oriented structure to a column-oriented one, facilitating easier analysis and presentation. To understand how to use a crosstab query, let's delve into a practical example.

Example: Pivoting Data to a Column-Oriented Format

Consider a table with the following structure:

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

You want to display these records 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

To achieve this, you can utilize a crosstab query. However, in your initial attempt, you retrieved multiple rows for each data point, which is not the desired outcome.

Corrected Crosstab Query

The following adjusted query will provide the desired result:

SELECT app_id, transaction_id, mobile_no,
  MAX(CASE WHEN node_id = 1 THEN entered_value END) AS Q1,
  MAX(CASE WHEN node_id = 2 THEN entered_value END) AS Q2,
  MAX(CASE WHEN node_id = 3 THEN entered_value END) AS Q3,
  MAX(CASE WHEN node_id = 4 THEN entered_value END) AS Q4,
  MAX(CASE WHEN node_id = 5 THEN entered_value END) AS Q5
FROM trn_user_log
GROUP BY app_id, transaction_id, mobile_no

Using Customer Attribute as Column Headers

You also expressed the desire to use values from the customer_attribute column as column headers. While the NAME_CONST function is not suitable for this purpose, you can utilize a technique that might achieve the desired result. However, please provide further clarification on your desired outcome to enable a precise solution.

The above is the detailed content of How to Create a MySQL Crosstab Query to Pivot Data from Rows to Columns?. For more information, please follow other related articles on the PHP Chinese website!