Transform vertical table to horizontal with MySQL -

i have tablea information, so:

time           data    value  -------------  ------  ------   120520142546    title   mr   120520142546    name    smith    120520142546    smoke   yes 180303140429    title   ms 180303140429    name    lea 180303140429    smoke   no 

i'm trying tableb (which created, want insert value) data same time value displayed in same row, (and tranform 'yes' 1 , 'no' 0) :

id      title  name    smoke ---     -----  -----   ----- 1       mr     smith    1 2       ms     lea      0 

i kind of understand can pivot thing couldn't find easy tutorial understand.

try this:

create table tableb (     id int not null auto_increment,     title varchar(255) not null,     name varchar(255) not null,     smoke tinyint(255) not null,     primary key (id) );  insert tableb (title, name, smoke) select t1.value title, t2.value name, (t3.value = 'yes') smoke tablea t1 join tablea t2 on t1.time = t2.time join tablea t3 on t1.time = t3.time t1.data = 'title' , t2.data = 'name' , t3.data = 'smoke'; 

this assumes 3 attributes available each timestamp, , no duplicate entries exist timestamp.