SQL server join tables and pivot (one more)

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.

%d bloggers like this: