Does anyone know of a way to build a pivot table using activerecord which would be remotely DB neutral? I ve tried to avoid using find_by_sql and DB specific queries but for a pivot table or crosstab query I have no idea how to do it in a way which is not specific to say MySQL. IE my mySQL find_by_sql breaks on a postgresql DB.
I found http://crosstab.rubyforge.org/ this obscure crosstab gem which might work, but I m wondering if anyone else has a better solution.
Example something rediculous like this which basically just flips the axis on a table:
SELECT availables.name, rooms.id,
MAX(IF(to_days(availables.bookdate) - to_days( 2009-06-13 ) = 0, availables.price, )) AS day1,
MAX(IF(to_days(availables.bookdate) - to_days( 2009-06-13 ) = 1, availables.price, )) AS day2,
MAX(IF(to_days(availables.bookdate) - to_days( 2009-06-13 ) = 2, availables.price, )) AS day3,
MAX(IF(to_days(availables.bookdate) - to_days( 2009-06-13 ) = 3, availables.price, )) AS day4,
MAX(IF(to_days(availables.bookdate) - to_days( 2009-06-13 ) = 4, availables.price, )) AS day5,
MAX(IF(to_days(availables.bookdate) - to_days( 2009-06-13 ) = 5, availables.price, )) AS day6,
MAX(IF(to_days(availables.bookdate) - to_days( 2009-06-13 ) = 6, availables.price, )) AS day7,
AVG(availables.price),SUM(availables.price)
FROM `availables`
INNER JOIN rooms
ON availables.room_id=rooms.id
WHERE availables.room_id = 18382
GROUP BY availables.name