MySql query to pull data in matrix form from database -
i have data in database 2 columns name , year. this,
name | year ----------- tim | 2001 ron | 2002 tim | 2003 ron | 2005 tim | 2004 pol | 2002 pol | 2001 tim | 2003 pol | 2004 tim | 2002
i want output result matrix this.
tim | pol | ron 2001 1 | 1 | 0 2002 1 | 1 | 1 2003 2 | 0 | 0 2004 1 | 1 | 0 2005 0 | 0 | 1
tim | pol | ron arranged in descending order based on cumulative sum i.e tim(5), pol(3), ron(2).
data not limited tim,pol,ron. there can n-different names.
select year, sum(name='tim') tim, sum(name='pol') pol, sum(name='ron') ron yourtable group year
please see fiddle here.
edit: if need dynamic query because exact number of values vary, use prepared statement this:
select concat('select year,', group_concat(sums), ' yourtable group year') ( select concat('sum(name=\'', name, '\') `', name, '`') sums yourtable group name order count(*) desc ) s @sql; prepare stmt @sql; execute stmt; deallocate prepare stmt;
fiddle here.
Comments
Post a Comment