How to Pivot Data in MySQL Using GROUP_CONCAT and Handle Dynamic Column Headers?

MySQL PIVOT/CROSSTAB QUERY

In data analysis, it is often necessary to transform data from a vertical format into a horizontal format, known as pivoting or cross-tabulating. In MySQL, this can be achieved using various techniques.

Creating a Pivot Table

Question 1: Consider a table containing the following columns: app_id, transaction_id, mobile_no, node_id, customer_attribute, and entered_value. The task is to create a pivot table grouped by app_id, transaction_id, and mobile_no, with the values from node_id becoming column headers and the corresponding entered_value being displayed in the cells.

Answer: This can be achieved using the GROUP_CONCAT function:

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

Using NAME_CONST

Question 2: Instead of using user_input1, user_input2, etc. as column headers, you want to use the values stored in customer_attribute.

Answer: This can be achieved using the NAME_CONST function, but an error was encountered due to incorrect arguments provided:

SELECT
  app_id,
  transaction_id,
  mobile_no,
  NAME_CONST(customer_attribute, (CASE WHEN node_id = 1 THEN entered_value ELSE NULL END))
FROM
  trn_user_log;

The function expects a constant value for the second argument, so a modification is needed:

SELECT
  app_id,
  transaction_id,
  mobile_no,
  CASE node_id
    WHEN 1 THEN NAME_CONST(customer_attribute, entered_value)
    ELSE NULL
  END AS customer_attribute
FROM
  trn_user_log;

The above is the detailed content of How to Pivot Data in MySQL Using GROUP_CONCAT and Handle Dynamic Column Headers?. For more information, please follow other related articles on the PHP Chinese website!