PIVOT datetime and ORDER column values of multiple rows

Question

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?

user_1570000 · Answer

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

PIVOT datetime and ORDER column values of multiple rows

1 Answers