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

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.

can a company have more than one record for a status? For example, can the next entry for id = 2 have status = new? — Jenn, Jul 30 '14 at 14:18
Welcome to Stack Overflow! We're glad to have you, especially since you obviously took such care to provide a clear, example-filled question. Keep it up! The SQL technique you are looking for is called a PIVOT - there are lots of questions here on similar things, I'd suggest reading this post as a start (http://stackoverflow.com/questions/17073134/sql-server-join-tables-and-pivot). Your question might be closed as a duplicate, since we've already got an answer, but searching for 'SQL PIVOT JOIN' will help in the future. — AHiggins, Jul 30 '14 at 14:18
Hi Jenn, the company isn't really important in this, more the Id. In your example, Id number two would never have a second status as "New" because it shows progress through the order's life. The next status would be something new. Thanks @AHiggins, I'll have a look at what you've suggested. I had seen some posts about PIVOT but couldn't find any examples with JOIN so I'll have a look. — ministe, Jul 30 '14 at 14:23

score 2 · Accepted Answer · answered Jul 30 '14 at 14:43

2

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
;

answered Jul 30 '14 at 14:43

Pavel Nefyodov

876
2
11
29

Thanks Pavel, I'd managed to get there based on AHiggins' comment to my question, but I'll mark this as the answer since it's specific and clear. Thanks! – ministe Jul 31 '14 at 07:20

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

1 Answers1