MYSQL Transpose query complex

Question

i have this table

|periode | prodi | jml_mhs |

|2009-2010 | TIF     | 390      |   
|2010-2011 | SIF     | 240      |   
|2011-2012 | TIN     | 160      |   

i want transpose my table to

|periode   | TIF   |  SIF | TIN |

|2009-2010 | 390   | 0    |0   
|2010-2011 | 0     | 240  |0   
|2011-2012 | 0     | 0    |160 

How can i do that?

Answer 1

This could be done using the following technique for known set of data and in your case its prodi

select 
periode,
max(case when prodi = 'TIF' then jml_mhs else 0 end ) as `TIF`,
max(case when prodi = 'SIF' then jml_mhs else 0 end ) as `SIF`,
max(case when prodi = 'TIN' then jml_mhs else 0 end ) as `TIN`
from table_name
group by periode