mysql - How do I arrange the data by different WHERE conditions into different columns? -

i have table have 3 columns (sid,subject,marks):

╔══════╦═════════╦═══════╗ ║ sid  ║ subject ║ marks ║ ╠══════╬═════════╬═══════╣ ║ 1a01 ║ eng     ║    66 ║ ║ 1a02 ║ eng     ║    75 ║ ║ 1a01 ║ math    ║    60 ║ ║ 1a03 ║ eng     ║    73 ║ ║ 1a02 ║ math    ║    35 ║ ║ 1a03 ║ math    ║    80 ║ ║ 1a01 ║ chi     ║    65 ║ ║ 1a02 ║ chi     ║    74 ║ ║ 1a03 ║ chi     ║    78 ║ ╚══════╩═════════╩═══════╝ 

and want group data sid in each row like:

╔══════╦═════╦═════╦══════╗ ║ sid  ║ chi ║ eng ║ math ║ ╠══════╬═════╬═════╬══════╣ ║ 1a01 ║  65 ║  66 ║   60 ║ ║ 1a02 ║  74 ║  75 ║   35 ║ ║ 1a03 ║  78 ║  73 ║   80 ║ ╚══════╩═════╩═════╩══════╝ 

i new in mysql, tried use subquery , union failed. can please give me hints?

if 3 subjects, can use case test subject every sid , aggregate result using max().

select  sid,         max(case when subject = 'chi' marks else null end) `chi`,         max(case when subject = 'eng' marks else null end) `eng`,         max(case when subject = 'math' marks else null end) `math`    tablename group   sid 

• sqlfiddle demo

but if have unknown number of subjects, dynamic sql uses prepare preferred.

set @sql = null; select   group_concat(distinct     concat(       'max(case when subject = ''',       subject,       ''' marks end) ',       concat('`', subject,'`')     )   ) @sql tablename;  set @sql = concat('select  sid, ', @sql, '                         tablename                     group   sid');  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

• sqlfiddle demo

output both query

╔══════╦═════╦═════╦══════╗ ║ sid  ║ chi ║ eng ║ math ║ ╠══════╬═════╬═════╬══════╣ ║ 1a01 ║  65 ║  66 ║   60 ║ ║ 1a02 ║  74 ║  75 ║   35 ║ ║ 1a03 ║  78 ║  73 ║   80 ║ ╚══════╩═════╩═════╩══════╝