0

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?

Taryn
  • 242,637
  • 56
  • 362
  • 405
holden
  • 13,471
  • 22
  • 98
  • 160

1 Answers1

1

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

Community
  • 1
  • 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • both of your examples seem to use pivot, and i'm pretty sure MySQL doesn't support it? – holden Jun 11 '09 at 08:39
  • 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 Jun 11 '09 at 13:24