How to Pivot MySQL Data into a Single Row with Dynamic Column Headers?

MySQL PIVOT/CROSSTAB QUERY

Problem 1: Retrieving Data in One Single Row

You can use the PIVOT function to transform your data into a single row format. The following query modifies your previous attempt to achieve this:

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;

This query groups the rows by app_id, transaction_id, and mobile_no and uses the MAX function to retrieve the maximum entered value for each node_id column, which corresponds to the "Q" values you want to display.

Problem 2: Using Column Values as Header Names

To use the values in the customer_attribute column as header names, you can use the CASE statement in conjunction with the AS keyword. The modified query is as follows:

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

In this query, the CASE statement is used within the MAX function to select the entered_value based on matching customer_attribute values. The AS keyword assigns the matching customer_attribute values as the column headers.

The above is the detailed content of How to Pivot MySQL Data into a Single Row with Dynamic Column Headers?. For more information, please follow other related articles on the PHP Chinese website!