SQL query the Drupal way, complex SELECT, conditional aggregate
Drupal does not provide database transposition, unfortunately. It's database access system is pretty good at filtering, joining and reordering the data, but not at restructuring them.

There are many patches and hacks for specific task, but none was general enough and needed enough to be accepted into core or views so far.

Your safest bet is to define a view, and then access it just like any other table. Drupal sees no difference as long as you don't write (and if you want to play, you could make MySQL to accept writes, too).