title: SQL transform aggregate values into individual columns
body: <p>So I have data normalized like this currently:  </p>
  
  <pre><code>+----------+-------+-------+
  | day      | color | value |
  +----------+-------+-------+
  | 1/1/2016 | red   | 1     |
  +----------+-------+-------+
  | 1/1/2016 | blue  | 2     |
  +----------+-------+-------+
  | 1/1/2016 | green | 3     |
  +----------+-------+-------+
  | 1/2/2016 | red   | 4     |
  +----------+-------+-------+
  | 1/2/2016 | blue  | 5     |
  +----------+-------+-------+
  | 1/2/2016 | green | 6     |
  +----------+-------+-------+  
  </code></pre>
  
  <p>I'd like to transform it into this layout for a report:  </p>
  
  <pre><code>+----------+-----+------+-------+
  | day      | red | blue | green |
  +----------+-----+------+-------+
  | 1/1/2016 | 1   | 2    | 3     |
  +----------+-----+------+-------+
  | 1/2/2016 | 4   | 5    | 6     |
  +----------+-----+------+-------+  
  </code></pre>
  
  <p>The code I'm using to do this is:  </p>
  
  <pre><code>with
  red as (
      select
      day
      ,value as red
      from mytable
      where color='red'
  ),
  
  blue as (
      select
      day
      ,value as blue
      from mytable
      where color='blue'
  ),
  
  green as (
      select
      day
      ,value as green
      from mytable
      where color='green'
  )
  
  select
  red.*
  ,blue.blue
  ,green.green
  from red red
  inner join blue blue
  on red.day=blue.day
  inner join green green
  on red.day=green.day  
  </code></pre>
  
  <p>so my question is, is there an easier or less wordy way to do this in Oracle? It seems a little silly for such a simple task! also, it's probably not very efficient.</p>