SQL simplify a characteristic function? or PIVOT tables?

Question

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?

Answer 1

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