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

Reshape the form into the column format

Challenge

: Search the data in the MySQL table in a perspective form formula, and convert the row into a column.

Question :

Given a table containing hostid, itemname, and the itemValue column, please press the results as shown below:

Solution

:

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

Step 1: Select the column of interested

OK will provide columns that will provide Y values ​​and x values ​​(itemname).

Step 2: Extend the basic table

Create additional columns for each unique X value.

Step 3: Grouping and aggregation

Groupid (hostid) and aggregate the extension table.

<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>

Step 4: Beautify (optional)

Replace the NULL value to zero to obtain a clearer appearance.

Precautions 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>

:

The values ​​used in the extension column and the default value of missing values ​​can be customized.

You can adjust the polymerization function (for example, MAX).

<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>

If you use multiple columns as a Y value or a large number of perspective columns are required, this solution may not be applicable.

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