MySQL – Rows to Columns

by

I tried to search posts, but I 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 will return

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

How do I query the database to return something like

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

13 Answers
13

Leave a Comment