mysql - My Sql Data output convert rows to column like pivot table -

i have table called marks. sample data table given below. in table subject field dynamic, name , number of subject may vary based on class.

  id        name        subject     marks ------    ---------    ----------  ---------        1001        john          maths       78 1001        john          english     88 1001        john          computer    92 1002        mary          maths       81 1002        mary         english      85 1002        mary         computer     90 

how can out put of above table data in below format. don't know whether simple question, beginner in sql , developing field. please help.

id    name         maths        english    computer ---- --------     ---------   ---------    ------------       1001  john          78             88           92 1002  mary          81             85           90  

a simpler solution use conditional aggregation so

select id,name, sum(case when s.subject='computer' s.marks else 0 end) computer, sum(case when s.subject='english' s.marks else 0 end) english, sum(case when s.subject='maths' s.marks else 0 end) maths  marks s group id,name; 

if don't know how many subjects there have create sql statement , run (dynamic sql). in easy stages

set @sumstr =  (select str ( select @rn:=@rn + 1 rn,@str:=concat(@str,'sum(case when s.subject=',char(39),s.subject,char(39),' s.marks else 0 end) ', s.subject, ',' ) str (select @rn:=0,@str:='') str,(select distinct subject marks) s order s.subject ) s order rn desc limit 1 ); 

creates sum statements enrich so

set @sqlstr = concat(                 'select id,name,'                 ,substring(@sumstr,1,length(@sumstr) -1)                 ,' marks s group id,name;'                 ) ; 

to create sql statement @ top of solution. prepare , execute it

prepare dynamic_statement @sqlstr; execute dynamic_statement;