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?

Possible duplicate of MySQL - Rows to Columns

Hamza Zafeer
– Hamza Zafeer

2016-06-28 01:46:19 +00:00
Commented Jun 28, 2016 at 1:46 — Hamza Zafeer
– Hamza Zafeer, Commented Jun 28, 2016 at 1:46

Tim Biegeleisen · Accepted Answer · 2016-06-28 02:02:22Z

2

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

edited Jun 28, 2016 at 2:02

answered Jun 28, 2016 at 1:47

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Shultz Over a year ago

Worked great! Thanks so much.

Blank · Accepted Answer · 2016-06-28 01:47:32Z

1

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

answered Jun 28, 2016 at 1:47

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Collectives™ on Stack Overflow

Pivot SQL data - groups rows into columns

2 Answers 2

SQLFiddle

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related