How to Perform Dynamic Crosstab Queries in MySQL Using PIVOT?

MySQL PIVOT/CROSSTAB Query

Problem Statement:

A table with data representing user inputs for different attributes is needed to be transformed into a Crosstab format, where each column header represents an attribute, and each row contains the corresponding user input values for that attribute. Additionally, the value of the customer_attribute column should be used as column headers instead of static labels.

Query 1: Transforming Data into Crosstab Format

To achieve the desired Crosstab format, you can use a PIVOT query as follows:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(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;

This query uses the PIVOT operator to transform the rows into columns, grouping by the app_id, transaction_id, and mobile_no columns.

Query 2: Using Custom Column Headers

To use the values from the customer_attribute column as column headers, you can modify the above query slightly:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(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;

This revised query replaces the static column headers (user_input1, user_input2, etc.) with the corresponding customer_attribute values.

Note:

• The prepared statements used in these queries ensure dynamic column creation and avoid potential syntax errors when handling an unknown number of columns.
• The NAME_CONST function used in your initial query is not applicable in this case, as it requires a constant value as the second argument, which is not feasible for dynamic column generation.

The above is the detailed content of How to Perform Dynamic Crosstab Queries in MySQL Using PIVOT?. For more information, please follow other related articles on the PHP Chinese website!