How to merge two rows in one if there is one same value in a column and change columns name

I’ve this query

SELECT
    pm.post_id,
    pm.meta_key,
    pm.meta_value
FROM 
   birr_postmeta as pm,
   birr_posts as p
WHERE pm.meta_key IN ('ratings_average', 'ratings_users')
    AND pm.meta_value <> 0
    AND pm.post_id = p.ID
ORDER BY pm.post_id ASC

that return me this:

What I want to obtain, from these table, is something like this

+--------+---------------+--------------+
|post_id |  rating_users |rating_average|
+--------+---------------+--------------+
|    7   |       1       |      5       |
|   35   |       3       |      4       |
|   101  |       2       |     4.5      |
|   127  |       2       |      4       |
|   225  |       1       |      4       |
+--------+---------------+------------+

How can I achieve this?

Answer

One method uses aggregation:

SELECT pm.post_id,
       MAX(CASE WHEN pm.meta_key = 'ratings_average' THEN pm.meta_value END) as ratings_average,
       MAX(CASE WHEN pm.meta_key = 'ratings_users' THEN pm.meta_value END) as ratings_users
FROM  birr_postmeta pm 
WHERE pm.meta_key IN ('ratings_average', 'ratings_users')
      pm.meta_value <> 0
GROUP BY pm.post_id ASC;

Note that no JOIN is necessary. But if you were to use one, you should use proper JOIN syntax.