views:

287

answers:

1

I have this a Table with a bunch of dates and prices:

Room Name, Price, Bookdate, etc

And I can transform it like so: (which essentially flips the columns)

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

This works perfectly and produces this:

name    id  day1  day2  day3  day4  day5  day6  day7  AVG(availables.price)  SUM(availables.price)
Bed     18382  23.00  21.00  21.00  21.00  21.00  21.00     21.571429  151.00

But how can I simplify it because I don't know the number of days? It could be 1 or 7? Any ideas?

+1  A: 

Have a look at this answer and the ones it links to.

Cade Roux
both of your examples seem to use pivot, and i'm pretty sure MySQL doesn't support it?
holden
You can still use an almost identical dynamic SQL technique to determine the number of columns and generate the SQL to execute with characteristic functions.
Cade Roux