SQL Server. Join two tables n a view, take rows from one and turn into columns [duplicate]

Question

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:

table 1

| ID | customer |    Date    | 
| 1  | company1 | 01/08/2014 | 
| 2  | company2 | 10/08/2014 |
| 3  | company3 | 25/08/2014 |

table 2

| ID | Status   | Days  |
| 1  |   New    |    6  |
| 1  | In Work  |   25  |
| 2  |   New    |   17  |
| 3  |   New    |   14  |
| 3  | In Work  |   72  |
| 3  | Complete |   25  |

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:

| 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    |

So what do I need to to to create this?

Thanks for any help, as I say I'm pretty new to this.

---

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.

Answer 1

WITH [TimeTable] AS (
   SELECT
      T1.ID,
      T1.[Date],
      T2.[Status] AS [Status],
      T2.[Days]
   FROM
      dbo.Table1 T1
   INNER JOIN dbo.Table2 T2
      ON T2.ID = T1.ID
)

SELECT *
FROM
   [TimeTable]
   PIVOT (MAX([Days]) FOR [Status] IN ([New], [Complete], [In Work])) TT
;