SQL query the Drupal way, complex SELECT, conditional aggregate I'm starting to invest time in learning custom Drupal entities like the ones explained here and I want to make sure that I am going to be able to do something in the future.

I am working with Drupal 7 with MySQL and have the entities API. Being tied to MySQL is okay.

Is it possible to perform a query like the one listed below in Drupal? If not, can I access a MySQL database view?

 select 
        `listProducts`.`productId` AS `productId`,

        group_concat(if((`listProducts`.`className` = 'manufacturer'),
                `listProducts`.`optionValue`,
                '')
            separator '') AS `manufacturer`,

        group_concat(if((`listProducts`.`className` = 'model'),
                `listProducts`.`optionValue`,
                '')
            separator '') AS `model`,

        group_concat(if((`listProducts`.`className` = 'sub model'),
                `listProducts`.`optionValue`,
                '')
            separator '') AS `sub model`,

         group_concat(if((`listProducts`.`className` = 'year'),
                `listProducts`.`optionValue`,
                '')
            separator '') AS `year`
    from
        `hqr`.`listProducts`
    group by `listProducts`.`productId`


It's sort of an intense select statement. The data I'm working with is in the form

productId  className      optionValue
---------------------------------------
1          manufacturer   abcCompany
1          model          aModel
2          manufacturer   xyzCompany
2          year           2005


and should come out like

productId  manufacturer  model  sub model  year
-------------------------------------------------
1          abcCompany    aModel    -        -
2          xyzCompany      -       -       2005


Is doing this possible using the Drupal tools? If so, knowing how would be a big bonus too. Somewhere in my travels I saw a Drupal patch to enable group_concat in Drupal views (if that changes anything).
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).