title: SQL joined table pivot without aggregate
body: <p>I have two tables:</p>
  
  <pre><code>Table1
  ID  TYPE
  1   ABC1
  2   ABC2
  3   ABC3
  
  Table2
  ID  Data
  1   100
  1   101
  2   10
  2   90
  </code></pre>
  
  <p>And I want the results to look like this:</p>
  
  <pre><code>ID  Data1  Data2
  1   100    101
  2   10     90
  </code></pre>
  
  <p>But I'm having a total mare with my attempts at creating the pivot.  So far I have:</p>
  
  <pre><code>With Inital_Data As ( 
  Select 
  A.ID,
  B.Data As Data1,
  B.Data As Data2
  
  From 
  Table1 A join
  Table2 B on
  A.ID = B.ID
  ) 
  
  Select * 
  From
   Initial_Data
   PIVOT
  (Max(ID) FOR Data IN (Data1,Data2)) p
  </code></pre>
  
  <p>I know this is rubbish but so far even the logic of what I'm trying to achieve is escaping me, let alone the syntax!  Can anyone give me a guiding hand?</p>