mysql - Get ouput result as column in SQL -

my table looks this.

 +---------+--------------------- | element_name | element_value |  +--------------+---------------+ | name         | name1         | | address      | address1      | | city         | city1         | | name         | name2         | | address      | address2      | | city         | city1         | +------------------------------- 

i need output this

+---------+--------------------- | name  |address    | city     | +--------------+---------------+ | name1 | address1  | city1    | | name2 | address2  | city2    | +------------------------------- 

note :- name, address, city example. can anything

can me out?

you can use group_concat() that:

select id    ,group_concat(case when element_name = 'name'                   element_value else null end) `name`    ,group_concat(case when element_name = 'address'                   element_value else null end) `address`    ,group_concat(case when element_name = 'city'                   element_value else null end) `city`   table1  group id; 

dynamic query (in case don't know number of element_name or there many element_name):

set @sql = null; select   group_concat(distinct     concat(       'group_concat(case when `element_name` = ''',       `element_name`,       ''' element_value else null end) `',       `element_name`, '`'     )   ) @sql table1;  set @sql = concat('select id, ', @sql,'                      table1                     group id                   ');  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

output:

| id |  name |  address |  city | --------------------------------- |  1 | name1 | address1 | city1 | |  2 | name2 | address2 | city1 | 

see this sqlfiddle