How use data in table as entity for key value in SQL?

Question

I have a table as following:

Is there any SELECT query to show table in following form?

Thanks a lot.

Answer 1

You can try following code :

SELECT post_id,
       (SELECT meta_value from test where meta_key='_total') AS _total,
       (SELECT meta_value from test where meta_key='_phone') AS _phone,
       (SELECT meta_value from test where meta_key='_address') AS _address 
FROM test
GROUP BY post_id

UPDATE Since the table is containing more post_id's, please check the following update.

SELECT post_id,
       (SELECT meta_value from test t2 where meta_key='_total' AND t1.post_id=t2.post_id) AS _total,
       (SELECT meta_value from test t2 where meta_key='_phone' AND t1.post_id=t2.post_id) AS _phone,
       (SELECT meta_value from test t2 where meta_key='_address' AND t1.post_id=t2.post_id) AS _address
FROM test t1
GROUP BY post_id

Answer 2

I think accessing the same table four times is not needed.

You can use the conditional aggregation as follows:

SELECT post_id,
       max(case when meta_key='_total' then meta_value end) AS _total,
       max(case when meta_key='_phone' then meta_value end) AS _phone,
       max(case when meta_key='_address' then meta_value end) AS _address 
FROM test
GROUP BY post_id