title: PIVOT in SQL Server with no aggregate function
body: <p>I have a table like this (lets call it <code>T1</code>):</p>
  
  <pre><code>   Date    | Category | Data
  -------------------------------
  2014-01-01 | 1        | 1.0
  2014-01-01 | 2        | 2.0
  2014-01-01 | 3        | 3.0
  2014-01-02 | 1        | 4.0
  2014-01-02 | 2        | 5.0
  2014-01-02 | 3        | 6.0
  </code></pre>
  
  <p>Note that <code>Data</code> are floating point numbers, not sequential, I just made them that way so it's easier to see where they land up in the result. There is also a table translating the <code>Category</code> numbers to names (lets say (<code>T2</code>):</p>
  
  <pre><code>Category | Name
  ----------------
  1        | A
  2        | B
  3        | C
  </code></pre>
  
  <p>And I would like a query that could return:</p>
  
  <pre><code>   Date    |   B   |   C   
  ---------------------------
  2014-01-01 |  2.0  |  3.0  
  2014-01-02 |  5.0  |  6.0
  </code></pre>
  
  <p>I thought about trying with <code>PIVOT</code> which I haven't used before, however, I can't find a way to do it without using a aggregation function. But then I though that since I only actually have a single row per <code>Date</code> / <code>Category</code> combo using an aggregation like <code>AVG</code> should work. This is my try based on <a href="http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx" rel="nofollow">these docs</a>:</p>
  
  <pre><code>SELECT [Date],
         [2] as B,
         [3] as C
  FROM (SELECT * FROM T1 WHERE Category &gt;= 2) AS SourceTable
  PIVOT
  (
      AVG(Data)
      FOR Category IN ([2], [3])
  ) AS PivotTable;
  </code></pre>
  
  <p>Which gets kind of close:</p>
  
  <pre><code>   Date    |   B   |   C   
  ---------------------------
  2014-01-01 |  2.0  |  NULL  
  2014-01-01 |  NULL |  3.0
  2014-01-02 |  5.0  |  NULL
  2014-01-02 |  NULL |  6.0
  </code></pre>
  
  <p>But how do I get rid of the nulls and get all the same dates to be on the same row?</p>