Vineet Vineet - 5 months ago 8
SQL Question

Selecting multiple values from the same column but under different conditions

I have a poorly formed table like this:

id| order_id | meta_key | meta_value
1 | 14 | 'cost'| 100
2 | 14 | 'tax' | 5
3 | 14 | 'sku' | q-1061


I want to select the cost, tax, sku for each order in the table, that is:

order_id|cost|tax|sku
14 | 100 | 5 | q-1061


Assuming there are 3 rows of for each order, what is the fastest way of doing it?

Answer

Maybe you want this, a simple way to pivot table:

SELECT
    order_id,
    MAX(CASE WHEN meta_key='cost' THEN meat_value END) AS cost,
    MAX(CASE WHEN meta_key='tax' THEN meat_value END) AS tax,
    MAX(CASE WHEN meta_key='sku' THEN meat_value END) AS sku
FROM yourtable
GROUP BY order_id