select - 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.

question from:https://stackoverflow.com/questions/65880998/how-use-data-in-table-as-entity-for-key-value-in-sql

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