title: SQL Server : display multiple rows in one row and multiple columns
body: <p>I've found similar questions but nothing that has a solution I've been able to make work for me. I've been out of practice and stuck on this.</p>
  
  <p>I have a table that tracks employee positions. Each employee can have any number of active positions. I want to display each employee as a single record with all their positions in that record spanning multiple columns.  </p>
  
  <p>A glance at the data shows that there are no employees with more than 6 active positions and since this is a one-time query, I should be able to work with that number.</p>
  
  <p>The positions all have a sequence number assigned but they're not in a nice handy order - inactive positions for an employee retain the sequence number.</p>
  
  <p>So right now I have something that looks like this:</p>
  
  <pre><code>    ID | Name | Title | Cat | Seq
      ------------------------------
      10 | John | Asst. | HR  | 13
      10 | John | Tutor | EDU | 17
      11 | Sue  | Mgr   | PA  | 6
      11 | Sue  | Adj.  | EDU | 7
      11 | Sue  | Tutor | EDU | 13
      ...
      11 | Sue  | Asst. | HR  | 22
  </code></pre>
  
  <p>But I want it to look like this:</p>
  
  <pre><code>    ID | Name | Title 1 | Cat 1 | Title 2 | Cat 2 | Title 3...| Title 6 | Cat 6
      ----------------------------------------------------------------------------
      10 | John | Asst.   | HR    | Tutor   | EDU   | NULL      | NULL    | NULL
      11 | Sue  | Mgr.    | PA    | Adj.    | EDU   | Tutor     | Asst.   | HR
  </code></pre>
  
  <p>Let me know what additional information I need to provide.</p>