This is probably a very basic question, but much searching has produced nothing useful.
I have a table called 'users' that has 3 columns
user ID Meta_key Meta_ value
1626 last_name smith
1626 first_name fred
1626 email_address firstname.lastname@example.org
1627 last_name blogs
1627 first_name george
1627 email_address email@example.com
And I would like the results to look like
1626 smith fred firstname.lastname@example.org
1627 blogs George email@example.com
How do I do a sql statement that produces this?
Your help would be much appreciated !!
select ln.user_id, ln.meta_value, fn.meta_value, em.meta_value
from users ln
inner join users fn on ln.user_id = fn.user_id
inner join users em on ln.user_id = em.user_id
Brilliant - thanks for the fast response, you have saved me much time ! Much appreciated !
if you are able, avoid using this kind of design on your table. entity - attribute - value model.
Performance is usually bad and query is complex
I totally agree on the issue of using EAV. It has it's place but must be used with caution for the reasons stated above.
SELECT [user ID],
MAX(CASE WHEN meta_key = 'last_name' THEN meta_value END) AS last_name,
MAX(CASE WHEN meta_key = 'first_name' THEN meta_value END) AS first_name,
MAX(CASE WHEN meta_key = 'email_address' THEN meta_value END) AS email_address
GROUP BY [user ID]
--ORDER BY last_name, first_name
Unfortunately the data structure belongs to woocommerce (a retail platform) within a wordpress website - I changed the context a bit to make it simple, but many thanks all for your comments and help.
Great that you guys are able to support !
I have used EAV for several years now and have had to deal with the issues related to getting data out. One of the best ways we have found that performs reasonable well is by using the PIVOT operator. Here is a link about how that works with the EAV data:
Info on Pivot for EAV Data
We use this all the time and it works well.