MySQL - Convert rows to columns

Question

I tried searching for posts but only found solutions for SQL Server/Access. I need a solution in MySQL (5.X).

I have a table (called history) with 3 columns: hostid, itemname, itemvalue.

If I do a select (select * from History) it returns

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

How to query the database to return similar content

+--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

Answer 1

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 be base 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.hostid column will become the y value in the pivot table. The values ​​in the history.itemname column will become x-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 values and x values
2. Extend the base table with additional columns - one column for each x 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, hostid provides the y value and itemname provides the x 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 is itemname:

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 with itemname = "A" has a non-null value for the new column A, and the other new column has a null value.

Step 3: Group and aggregate the extended table

. We need grouping by 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 ugly NULL.

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 example What "neutral" values ​​to use in extra columns. I used
NULL
• , but it could also be 0 or "", depending on your situation What aggregate function to use when grouping. I used
sum
• , but count and max are also often used (max is 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 to select them)
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.