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?