title: SQL Server. Join two tables n a view, take rows from one and turn into columns
body: <p>I'm pretty new to SQL Server so don't really know what I'm doing with this.  I have two tables, which might look like this:</p>
  
  <p>table 1</p>
  
  <pre><code>| ID | customer |    Date    | 
  | 1  | company1 | 01/08/2014 | 
  | 2  | company2 | 10/08/2014 |
  | 3  | company3 | 25/08/2014 |
  </code></pre>
  
  <p>table 2</p>
  
  <pre><code>| ID | Status   | Days  |
  | 1  |   New    |    6  |
  | 1  | In Work  |   25  |
  | 2  |   New    |   17  |
  | 3  |   New    |   14  |
  | 3  | In Work  |   72  |
  | 3  | Complete |   25  |
  </code></pre>
  
  <p>What I need to do is join based on the ID, and create new columns to show how long each ID has been in each status.  Every time an order goes to a new status, a new line is added and the number of days is counted as in the 2nd table above.  What I need to create from this, should look like this:</p>
  
  <pre><code>| ID | customer |    Date    | New | In Work | Complete |
  | 1  | company1 | 01/08/2014 |  6  |    25   |          |
  | 2  | company2 | 10/08/2014 |  17 |         |          |
  | 3  | company3 | 25/08/2014 |  14 |    72   |    25    |
  </code></pre>
  
  <p>So what do I need to to to create this?</p>
  
  <p>Thanks for any help, as I say I'm pretty new to this.</p>
  
  <hr>
  
  <p>I would suggest that AHiggins' link is a better candidate to mark this as a dupe rather than the one that's actually been selected because his link involves a join.</p>