I have the following WordPress tables:
wp_f7h0p6_users
---------------------------------------
| ID | user_login | user_email |
| 1 | johnd | johnd@example.com |
| 2 | foob | foob@example.com |
wp_f7h0p6_usermeta
------------------------------------------------------
| umeta_id | user_id | meta_key | meta_value |
| 1 | 1 | first_name | John |
| 2 | 1 | last_name | Doe |
I want to get a SELECT result like this, for which I need to transpose the wp_f7h0p6_usermeta
table somehow.
-----------------------------------------------------------
| user_login | user_email | first_name | last_name |
| johnd | johnd@example.com | John | Doe |
| foob | foob@example.com | Foo | Bar |
I tried something like this. But it doesn't group the results into one record per user:
SELECT DISTINCT
user_login,
(CASE M.meta_key WHEN 'first_name' THEN M.meta_value END) first_name,
(CASE M.meta_key WHEN 'last_name' THEN M.meta_value END) last_name,
(CASE M.meta_key WHEN 'billing_first_name' THEN M.meta_value END) billing_first_name
FROM wp_f7h0p6_users U
INNER JOIN wp_f7h0p6_usermeta M ON U.ID = M.user_id
The result looks like this:
-----------------------------------------------------------
| user_login | user_email | first_name | last_name |
| johnd | johnd@example.com | | |
| johnd | | John | |
| johnd | | | Doe |
How can I group those correctly?
Read more here: https://stackoverflow.com/questions/66280310/sql-server-join-tables-and-pivot-one-more
Content Attribution
This content was originally published by alev at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.