I have a table with values which are not in order
| Id | DateTime | Status |
|---|---|---|
| 1 | 2022-03-01 18:00:00.000 | Stop1 |
| 2 | 2022-03-01 08:00:00.000 | Start |
| 3 | 2022-03-01 20:00:00.000 | Stop2 |
| 4 | 2022-03-02 09:00:00.000 | Start |
| 5 | 2022-03-01 10:00:00.000 | Stop2 |
| 6 | 2022-03-02 11:00:00.000 | Finish |
| 7 | 2022-03-01 14:00:00.000 | Start |
| 8 | 2022-03-02 10:00:00.000 | Stop1 |
where Status can be 'Start', 'Stop1', 'Stop2', or 'Finish'.
I need the timeline like this, where the values are pivoted in the order (from the earliest to the latest; id is not relevant at this point)
| Id | Start | Stop1 | Stop2 | Finish |
|---|---|---|---|---|
| 2 | 2022-03-01 08:00:00 | NULL | 2022-03-01 10:00:00 | NULL |
| 7 | 2022-03-01 14:00:00 | 2022-03-01 18:00:00 | 2022-03-01 20:00:00 | NULL |
| 4 | 2022-03-02 09:00:00 | 2022-03-02 10:00:00 | NULL | 2022-03-02 11:00:00 |
After I PIVOTed it in SQL Server
SELECT *
FROM (
SELECT Id, DateTime, Status FROM table
) t
PIVOT (
MAX(DateTime)
FOR Status IN (Start, Stop1, Stop2, Finish)
) p
I got
| Id | Start | Stop1 | Stop2 | Finish |
|---|---|---|---|---|
| 2 | 2022-03-01 08:00:00 | NULL | NULL | NULL |
| 5 | NULL | NULL | 2022-03-01 10:00:00 | NULL |
| 7 | 2022-03-01 14:00:00 | NULL | NULL | NULL |
| 1 | NULL | 2022-03-01 18:00:00 | NULL | NULL |
| 3 | NULL | NULL | 2022-03-01 20:00:00 | NULL |
| 6 | NULL | NULL | NULL | 2022-03-02 11:00:00 |
| 8 | NULL | 2022-03-02 10:00:00 | NULL | NULL |
| 4 | 2022-03-02 09:00:00 | NULL | NULL | NULL |
How can I get that timeline?
CodePudding user response：
Perhaps this will help. The window functions can be invaluable
Also, remember to "FEED" your pivot with only the required columns.
Example
Select *
From (
Select id = min(case when Status='Start' then ID end) over (partition by Grp)
,DateTime
,Status
From (
Select *
,Grp = sum( case when [Status]='Start' then 1 else 0 end) over (order by datetime)
from YourTable
) A
) src
Pivot ( max(DateTime) FOR Status IN (Start, Stop1, Stop2, Finish) ) p
Results
