Pivot SQL data - groups rows into columns

Question

I have data stored in a mySQL database in the following format:

+------------+------------+-----------+
|    id      |   field    |   value   |
+============+============+===========+
|     1      |   first    |    Bob    |
+------------+------------+-----------+
|     1      |   last     |   Smith   |
+------------+------------+-----------+
|     2      |   first    |    Jim    |
+------------+------------+-----------+
|     2      |   last     |   Jones   |
+------------+------------+-----------+

and I would like it returned as follows:

+------------+------------+-----------+
|    id      |   first    |   last    |
+============+============+===========+
|     1      |    Bob     |   Smith   |
+------------+------------+-----------+
|     2      |    Jim     |   Jones   |
+------------+------------+-----------+

I know this seems like a silly way to store data, but it's just a simple example of what I really have. The table is formatted this way from a WordPress plugin, and I'd like to make it work without having to rewrite the plugin.

From what I've read, I can't use PIVOT with mySql. Is there something similar to PIVOT that I can use to achieve what I'm going for?

Answer 1

Try this pivot query:

SELECT id,
    MAX(CASE WHEN field = 'first' THEN value ELSE NULL END) AS first,
    MAX(CASE WHEN field = 'last'  THEN value ELSE NULL END) AS last
FROM yourTable
GROUP BY id

Follow the link below for a running demo:

SQLFiddle

Answer 2

Try this;)

select
    id,
    max(if(field='first', value, null)) as first,
    max(if(field='last', value, null)) as last
from yourtable
group by id

SQLFiddle DEMO HERE