title: Query to merge multiple rows into into distinct rows with multiple columns
body: <p>I don't consider myself a complete SQL novice, but I have been staring at this problem for two days now, on and off, and I am beginning to think maybe I am! </p>
  
  <p>Using the following two tables:</p>
  
  <pre><code>ID    Category
  1     Animal
  2     Color
  3     Sport
  
  Name    ID    Value
  Fred    1     Cat
  Fred    2     Blue
  Fred    3     Football
  Sally   1     Dog
  Sally   3     Jogging
  James   2     Green
  Anne    3     Swimming
  </code></pre>
  
  <p>I have been unable to find any combination of group by, sub-query, union, cte or pivot commands that will merge the multiple rows returned from an  ‘(ID =  1 or ID = 2)’ query into a distinct rows with multiple columns.</p>
  
  <p>E.G.:</p>
  
  <pre><code>where (ID=1 or ID=2)
  
  Name     Animal    Color
  Fred     Cat       Blue
  Sally    Dog       NULL
  James    NULL      Green
  </code></pre>
  
  <p>Can anyone please advise me if there is an efficient SQL solution to this or am I wasting my time on something that should be handled by code in the report?</p>
  
  <p>Thanks</p>