Using MAX and CASE to pivot MySQL data stored in EAV Model

Last week, I covered pivoting tabular data in JavaScript. But, often times, data is not stored like a CSV or excel sheet in a database. When flexibility is needed to cover potential future changes, like adding many new columns or removing old ones, we often see the EAV model being used to store data.

Consider the previous example of storing car data in rows with columns: id, make, color, and age. If you are just adding more rows, this works well. But, if you will be adding an uncertain number of new columns in the future (model, size, etc), or removing columns, it might make sense to use the EAV model.

The image above demonstrates the difference between EAV and relational model representation. If the data is stored entity-attribute-value style and you want to see all attributes of a given user as one row with attributes as columns, you will need to pivot, shift, or transform that data. WordPress uses EAV so I will focus my example on wp_usermeta table which is used to store different settings and attributes of a WordPress user.

How do you write a query that pivots an EAV table into a multi-column representation showing attributes of each user per row?

There are a few ways to pivot data with MySQL but I will focus on using MAX, CASE, and WHEN, THEN, END to return tabular data. The only thing you need is to provide the columns or attributes. If you want to query the database for first name and last name of each user (represented in a row and shown in the image above), this would be your SQL query:


SELECT user_id,
MAX(CASE WHEN meta_key='first_name' THEN meta_value END) as first,
MAX(CASE WHEN meta_key='last_name' THEN meta_value END) as last
FROM `wp_usermeta`
GROUP BY user_id


Now adjust to your needs.

External:
The EAV Data Model
Extensible Data Modeling
Pivot – Rows to Columns