How to Pivot a Table in MySQL: Transforming Rows into Columns?

reshape the table to the converting line to column

Question:

Convert a table with three columns into a data perspective table, where the line is turned into columns.

Example:

Input table:

The required output (data perspective table):

hostid	itemname	itemvalue
1	A	10
1	B	3
2	A	9
2	C	40

Solution:

hostid	A	B	C
1	10	3	0
2	9	0	40

MySQL's data perspective function involves a three -step process:

Select interested columns:

1. Basic tables with extra column expansion:

<code class="language-sql">SELECT hostid, itemname, itemvalue
FROM history;</code>

Add a column corresponding to each unique itemname.

2. Grouping and aggregating expansion table:

grouping in the hostid and the sum of the value of each column.

<code class="language-sql">CREATE VIEW history_extended AS (
  SELECT
    history.*,
    CASE WHEN itemname = "A" THEN itemvalue END AS A,
    CASE WHEN itemname = "B" THEN itemvalue END AS B,
    CASE WHEN itemname = "C" THEN itemvalue END AS C
  FROM history
);</code>

3. Optional: beautifying the aggregate table (replace the NULL value to 0)

Note and restrictions:

<code class="language-sql">CREATE VIEW history_itemvalue_pivot AS (
  SELECT
    hostid,
    SUM(A) AS A,
    SUM(B) AS B,
    SUM(C) AS C
  FROM history_extended
  GROUP BY hostid
);</code>

4. This solution assumes that the data perspective column has a specific value (here is ItemValue).
It supports multiple Y values, but it is limited to manually adding data perspective columns.

<code class="language-sql">CREATE VIEW history_itemvalue_pivot_pretty AS (
  SELECT
    hostid,
    COALESCE(A, 0) AS A,
    COALESCE(B, 0) AS B,
    COALESCE(C, 0) AS C
  FROM history_itemvalue_pivot
);</code>

The use of a large number of data to generate data seeing tables may be challenging.

The above is the detailed content of How to Pivot a Table in MySQL: Transforming Rows into Columns?. For more information, please follow other related articles on the PHP Chinese website!