Mysql - TABLE field layout using sql statement -

is there format sql statement result given following........

table1   id ticket_number   1   2001   2   2002    table2   tn    name                 create_time   2001  sms_to_customer      2013-05-05 10:10:19   2001  sms_to_officer1      2013-05-05 11:17:01   2001  sms_to_officer2      2013-05-05 12:14:05   2002  sms_to_officer1      2013-05-05 09:17:01   2002  sms_to_officer2      2013-05-05 09:30:05   

i want result like........

ticket_number   sms_to_customer      sms_to_officer1        sms_to_officer2   2001            2013-05-05 10:10:19  2013-05-05 11:17:01    2013-05-05 12:14:05   2002            na                   2013-05-05 09:17:01    2013-05-05 09:30:05  

if values column name on table2, can use static version of query,

select  tn,         coalesce(max(case when name = 'sms_to_customer' create_time end), 'na') `sms_to_customer`,         coalesce(max(case when name = 'sms_to_officer1' create_time end), 'na') `sms_to_officer1`,         coalesce(max(case when name = 'sms_to_officer2' create_time end), 'na') `sms_to_officer2`    table2 group   tn 

• sqlfiddle demo

otherwise, use dynamic sql if have values other that

set @sql = null; select   group_concat(distinct     concat(       'coalesce(max(case when name = ''',       name,       ''' create_time end), ''na'') ',       concat('`',name,'`')     )   ) @sql table2;  set @sql = concat('select  tn, ', @sql, '                     table2                    group tn');  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

• sqlfiddle demo

output

╔══════╦═════════════════════╦═════════════════════╦═════════════════════╗ ║  tn  ║   sms_to_customer   ║   sms_to_officer1   ║   sms_to_officer2   ║ ╠══════╬═════════════════════╬═════════════════════╬═════════════════════╣ ║ 2001 ║ 2013-05-05 10:10:19 ║ 2013-05-05 11:17:01 ║ 2013-05-05 12:14:05 ║ ║ 2002 ║ na                  ║ 2013-05-05 09:17:01 ║ 2013-05-05 09:30:05 ║ ╚══════╩═════════════════════╩═════════════════════╩═════════════════════╝