title: Pivot MySQL rows to columns in view
body: <p>I have the following tables: days, periods, prices, brands, vehicles.</p>
  
  <p>The Price model belongsTo Day and Period.</p>
  
  <p>DB Schema:</p>
  
  <pre><code>CREATE TABLE `days` (
    `id` int(3) NOT NULL AUTO_INCREMENT,
    `low` int(11) NOT NULL DEFAULT '0',
    `high` int(11) DEFAULT '0',
    `brand_id` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
  );
  CREATE TABLE `periods` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `start` date NOT NULL DEFAULT '0000-00-00',
    `finish` date NOT NULL DEFAULT '0000-00-00',
    `brand_id` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
  );
  CREATE TABLE `brands` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `brand` varchar(255) NOT NULL DEFAULT '',
    `slug` varchar(20) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
  );
  CREATE TABLE `vehicles` (
    `id` int(9) NOT NULL AUTO_INCREMENT,
    `size_id` int(11) DEFAULT NULL,
    `brand_id` int(11) DEFAULT NULL,
    `slug` varchar(50) NOT NULL,
    `model` varchar(50) DEFAULT NULL,
    `image` varchar(50) DEFAULT NULL,
    `short_specs` text,
    `specs` text,
    `publish` tinyint(1) DEFAULT '1',
    PRIMARY KEY (`id`)
  );
  CREATE TABLE `prices` (
    `id` int(3) NOT NULL AUTO_INCREMENT,
    `vehicle_id` int(3) NOT NULL DEFAULT '0',
    `day_id` int(3) NOT NULL DEFAULT '0',
    `period_id` int(3) NOT NULL DEFAULT '0',
    `price` decimal(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`)
  );
  </code></pre>
  
  <p>I'm using virtualFields for the Day and Period models:</p>
  
  <pre><code>// day.php
  var $virtualFields = array(
      'name' =&gt; "CONCAT(Day.low,IF(Day.high IS NULL, '+', ' to '),IF(Day.high IS NULL, '', Day.high))"
  );
  
  // period.php  dates are localized using setLocale and strftime in afterFind()
  var $virtualFields = array(
      'name' =&gt; "CONCAT(Period.start,'|', Period.finish)"
  );
  function afterFind($results) {
      foreach ($results as $key =&gt; $val) {
          if (isset($val['Period']['name'])) {
              $dates = explode('|',$val['Period']['name']);
              $results[$key]['Period']['name'] = strftime('%d %b %Y',strtotime($dates[0])). ' – ' . strftime('%d %b %Y',strtotime($dates[1]));
          }
      }
      return $results;
  }
  </code></pre>
  
  <p>In the Price model if I do:</p>
  
  <pre><code>function price($id) {
      $this-&gt;set('prices', $this-&gt;find('all',array('conditions' =&gt; ('Price.vehicle_id' =&gt; $id))));
  }
  </code></pre>
  
  <p>This is shown in the view</p>
  
  <pre>Day            Period                      Price
  5 - 20          01 May 2011 to 31 Aug 2011  $87.00
  21 to 27        01 May 2011 to 31 Aug 2011  $66.00
  28+             01 May 2011 to 31 Aug 2011  $63.00
  5 - 20          01 Sep 2011 to 30 Sep 2011  $177.00
  21 to 27        01 Sep 2011 to 30 Sep 2011  $165.00
  28+             01 Sep 2011 to 30 Sep 2011  $155.00
  5 - 20          01 Oct 2011 to 31 Oct 2011  $322.00
  21 to 27        01 Oct 2011 to 31 Oct 2011  $310.00
  28+             01 Oct 2011 to 31 Oct 2011  $300.00
  </pre>
  
  <p>How can I present the data like this?</p>
  
  <pre>                           5 - 20 days     21 - 27 days    28+ days
  01 May 2011 to 31 Aug 2011  $87.00      $66.00      $63.00
  01 Sep 2011 to 30 Sep 2011  $177.00         $165.00         $155.00
  01 Oct 2011 to 31 Oct 2011  $322.00         $310.00         $300.00
  </pre>
  
  <p>I have been trying to learn about <a href="http://www.artfulsoftware.com/infotree/queries.php#78" rel="nofollow">pivot tables</a> but am struggling to do this in CakePHP.  Any suggestions would be much appreciated. </p>