I have data that looks like this:
| ID | act1 | act1_st | act1_end | act2 | act2_st | act2_end |
|---|---|---|---|---|---|---|
| 1 | A | 1/1/21 08 | 1/1/21 09 | B | 1/1/21 10 | 1/1/21 11 |
| 1 | C | 1/4/21 08 | 1/4/21 09 | B | 1/5/21 10 | 1/5/21 11 |
| 2 | A | 1/2/21 08 | 1/2/21 09 | B | 1/3/21 10 | 1/3/21 11 |
| 2 | B | 1/6/21 08 | 1/6/21 09 | NULL | NULL | NULL |
I would like to transpose the start and end times, as well as the corresponding act_ID:
| ID|act_ID|start |end |
|:–|:—-:|:——-:|:——–:|
| 1 | A |1/1/21 08|1/1/21 09 |
| 1 | B |1/1/21 10|1/1/21 11 |
| 1 | C |1/4/21 08|1/4/21 09 |
| 1 | B |1/5/21 10|1/5/21 11 |
| 2 | A |1/2/21 08|1/2/21 09 |
| 2 | B |1/3/21 10|1/3/21 11 |
| 2 | B |1/6/21 08|1/6/21 09 |
I have been attempting to use unpivot, for example unpivoting all of act1_start and act2_start into a ‘start’ column, but having trouble joining the start end times such that the start is before the end, and the act_ID is linked to the correct interval.
Any advice would be greatly appreciated. thank you