I will add a longer, more detailed description of the steps to resolve this issue. I apologize if it's too long.

---

I'll start with the foundation you gave and use it to define a few terms that I will use throughout the rest of this article. This will bebase table:

select * from history; +--------+----------+-----------+ | hostid | itemname | itemvalue | +--------+----------+-----------+ | 1 | A | 10 | | 1 | B | 3 | | 2 | A | 9 | | 2 | C | 40 | +--------+----------+-----------+

This will be our goal,Beautiful pivot table:

select * from history_itemvalue_pivot; +--------+------+------+------+ | hostid | A | B | C | +--------+------+------+------+ | 1 | 10 | 3 | 0 | | 2 | 9 | 0 | 40 | +--------+------+------+------+

The value in the

history.hostidcolumn will become they valuein the pivot table. The values in thehistory.itemnamecolumn will becomex-values(for obvious reasons).

---

When I have to solve the problem of creating a pivot table, I use a three-step process to solve it (with an optional fourth step):

1. Select the columns of interest, i.e.y valuesandx values
2. Extend the base table with additional columns - one column for eachx value
Group and aggregate the extended table - one set per
3. y value(Optional) Beautify the aggregation table
Let's apply these steps to your problem and see what we get:

Step 1: Select the columns of interest

. In the desired result,hostidprovides they valueanditemnameprovides thex value.

Step 2: Extend the base table

with additional columns. We usually want one column for each x value. Recall that our x-value column isitemname:

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 ); select * from history_extended; +--------+----------+-----------+------+------+------+ | hostid | itemname | itemvalue | A | B | C | +--------+----------+-----------+------+------+------+ | 1 | A | 10 | 10 | NULL | NULL | | 1 | B | 3 | NULL | 3 | NULL | | 2 | A | 9 | 9 | NULL | NULL | | 2 | C | 40 | NULL | NULL | 40 | +--------+----------+-----------+------+------+------+

Note that we did not change the number of rows - we just added extra columns. Also note the pattern of

NULL

- the row withitemname = "A"has a non-null value for the new columnA, and the other new column has a null value.

Step 3: Group and aggregate the extended table

. We needgroupingby hostid since it provides the y value:

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 ); select * from history_itemvalue_pivot; +--------+------+------+------+ | hostid | A | B | C | +--------+------+------+------+ | 1 | 10 | 3 | NULL | | 2 | 9 | NULL | 40 | +--------+------+------+------+

(Note that we now have one row per y value.)

Okay, we're almost there! We just need to get rid of those uglyNULL.

Step 4: Beautify

. We will replace any null values with zeros so that the result set looks better:

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 ); select * from history_itemvalue_pivot_pretty; +--------+------+------+------+ | hostid | A | B | C | +--------+------+------+------+ | 1 | 10 | 3 | 0 | | 2 | 9 | 0 | 40 | +--------+------+------+------+

We’re done – we’ve built a beautiful pivot table using MySQL.

Things to note when applying this process:

---

What values to use in extra columns. I used

itemvalue
• in this exampleWhat "neutral" values to use in extra columns. I used
NULL
• , but it could also be0or"", depending on your situationWhat aggregate function to use when grouping. I used
sum
• , butcountandmaxare also often used (maxis often used when building a single row) spread across multiple rows Object")Use multiple columns to represent y values. This solution is not limited to using a single column for the y values - just insert the extra columns into the
group by
• clause (and don't forget toselectthem)
Known limitations:

This solution does not allow n columns in the pivot table - each pivot column needs to be added manually when extending the base table. So for 5 or 10 x values this solution is fine. 100 yuan, not very good. There are some solutions to using stored procedures to generate queries, but they are ugly and difficult to do correctly. I don't currently know of any good way to solve this problem when the pivot table needs to have many columns.