condensing query using SQL

Question

I have a table with 4 things I want... the Name, Price, QTY, and a specific Date

There are lots of Entries per date:

Name          Price  Date

Twin Private  $25    06/02/09
Double        $35    06/02/09
Single        $20    06/02/09
Twin Private  $25    06/03/09
Double        $35    06/03/09
Single        $20    06/03/09
Twin Private  $25    06/04/09
Double        $35    06/04/09
Single        $20    06/04/09

How can I condense it into:

Name          Price_06/02/09  Price_06/03/09  Price_06/04/09

Twin Private  $25             $25             $30
Double        $35             $35             $50
Single        $20             $20             $40

Answer 1

I think this will do it:

select  Name,
        max(Price_06/02/09) as Price_06/02/09,
        max(Price_06/03/09) as Price_06/03/09,
        max(Price_06/04/09) as Price_06/04/09
from    (
        select    Name,
                  case Date
                      when '06/02/09' then Price
                      else null
                  end as Price_06/02/09,
                  case Date
                      when '06/03/09' then Price
                      else null
                  end as Price_06/03/09,
                  case Date
                      when '06/04/09' then Price
                      else null
                  end as Price_06/04/09
        from      Rates) as Aggregated
group by 
        Name

This works in two stages, the inner query stretches the data out so you'll end up with:

Name          Price_06/02/09  Price_06/03/09  Price_06/04/09

Twin Private  $25             null            null
Double        $35             null            null
Single        $20             null            null
Twin Private  null            $25             null
Double        null            $35             null
Single        null            $20             null
Twin Private  null            null            $25
Double        null            null            $35
Single        null            null            $20

Then the outer query groups by the name to flatten it to:

Name          Price_06/02/09  Price_06/03/09  Price_06/04/09

Twin Private  $25             $25             $25
Double        $35             $35             $35
Single        $20             $20             $20

Answer 2

To addition to Garry Shutler's answer it could be useful to change columns and rows in places because there are definitely only limited quantity of room names but we can't say this about dates.

Answer 3

I ended up using something like this:

I'm using mysql, does it support PIVOT?

SELECT


    name, room_id,

      MAX(IF(to_days(bookdate) - to_days('2009-06-24') = 0, price, '')) AS Day1,
      MAX(IF(to_days(bookdate) - to_days('2009-06-24') = 1, price, '')) AS Day2,
      MAX(IF(to_days(bookdate) - to_days('2009-06-24') = 2, price, '')) AS Day3,
      MAX(IF(to_days(bookdate) - to_days('2009-06-24') = 3, price, '')) AS Day4,
      MAX(IF(to_days(bookdate) - to_days('2009-06-24') = 4, price, '')) AS Day5,
      MAX(IF(to_days(bookdate) - to_days('2009-06-24') = 5, price, '')) AS Day6,
      MAX(IF(to_days(bookdate) - to_days('2009-06-24') = 6, price, '')) AS Day7, spots
    FROM `availables`
    GROUP BY name