How to select row values conditionally and change them to custom columns?

I have two tables orders, product_meta that are as follows:

orders:

order_id product_id
   1        45
   1        46
   2        46  

product_meta:

product_id meta_key meta_value
    45       price    1199
    45       name     GPU-1
    46       price    1299
    46       name     GPU-2

I want a table like this:

order_id product_id product_name product_price
     1        45       GPU-1         1199
     1        46       GPU-2         1299
     2        46       GPU-2         1299

The query that I have tried:

select
    o.order_id,
    o.product_id,
    pm.meta_value as 'product_name'
from  orders o
join product_meta pm on pm.product_id = o.product_id AND pm.meta_key = 'name'

but this only works for one field(name) so how can I modify this so as to include price and other meta values?

Answer

One method is two joins:

select o.*, pmn.meta_value as product_name, pmp.meta_value as product_price
from orders o left join
     product_meta pmn
     on pmn.product_id = o.product_id and pmn.meta_key = 'name' left join
     product_meta pmp
     on pmp.product_id = o.product_id and pmp.meta_key = 'price';

This uses left joins so all orders will be included, even if the metadata is missing either name or price or both.