How to Transform MySQL Column Data into a Row-Based Pivot Table?

Convert MySQL column data to row format (pivot table)

This article describes how to convert a MySQL table containing multi-column data into row and column format data. The resulting structure is called a pivot table, and it provides a concise yet comprehensive view of the data.

Problem statement:

We have a table with multiple columns, each column represents a data category, our goal is to convert it into a pivot table format, where the rows represent categories (col1, col2, col3, col4) and the columns represent months (Jan , Feb).

Solution Framework:

To implement this conversion in MySQL we must follow the following two-step process:

Anti-Perspective (UNION ALL):

1. Split the input columns (col1, col2, etc.) into separate rows, creating a new column ("descrip") for the categories and another column for the corresponding values. Use the UNION ALL operator to combine these rows into a single table.

Perspective (aggregation and CASE):

2. Apply an aggregate function, such as MAX(), to the unpivot table while using a CASE statement to determine which values ​​belong to which month. This allows us to create the required columns (Jan, Feb).

Implemented queries:

Anti-Perspective (UNION ALL):

<code class="language-sql">SELECT
  id,
  month,
  col1 AS value,
  'col1' AS descrip
FROM
  yourtable
UNION ALL
SELECT
  id,
  month,
  col2 AS value,
  'col2' AS descrip
FROM
  yourtable
UNION ALL
SELECT
  id,
  month,
  col3 AS value,
  'col3' AS descrip
FROM
  yourtable
UNION ALL
SELECT
  id,
  month,
  col4 AS value,
  'col4' AS descrip
FROM
  yourtable;</code>

Perspective (aggregation and CASE):

<code class="language-sql">SELECT
  descrip,
  MAX(CASE WHEN month = 'Jan' THEN value ELSE 0 END) AS Jan,
  MAX(CASE WHEN month = 'Feb' THEN value ELSE 0 END) AS Feb
FROM
  (
    SELECT
      id,
      month,
      col1 AS value,
      'col1' AS descrip
    FROM
      yourtable
    UNION ALL
    SELECT
      id,
      month,
      col2 AS value,
      'col2' AS descrip
    FROM
      yourtable
    UNION ALL
    SELECT
      id,
      month,
      col3 AS value,
      'col3' AS descrip
    FROM
      yourtable
    UNION ALL
    SELECT
      id,
      month,
      col4 AS value,
      'col4' AS descrip
    FROM
      yourtable
  ) AS source
GROUP BY
  descrip;</code>

Result:

This query will convert the input table into pivot table format:

DESCRIP	Jan	Feb
col1	A	C
col2	B	A
col3	0	G
col4	B	E

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