How to select data from a key value pair table

Question

Table A

id,parentID, key, value
1, 2, name, name1
2, 2, age, 20
3, 2, place, place1

50, 7, name, namex
51, 7, age, 20
52, 7, place, place1
........
101, 5, name, namez
102, 5, age, 23
103, 5, place, place2

I need to get all the date having plave = place1 and age = 20 in the bellow format

parentid, name, age, place
2, name1, 20, place1
7, namex, 20, place1

How to write the Mysql SQL query please help me

Answer 1

You can use conditional aggregation to get all the info for a parentid on to one row and then use a where clause for the required condition.

select * from (
select parentid
,max(case when key='name' then value end) as name
,max(case when key='age' then value end) as age
,max(case when key='place' then value end) as place
from tableA
group by parentid 
) t
where place='place1' and age=20

This assumes there is only one row per key per parentid in the table.