Complex Pivot table in SQL Server

Question

I have some data that looks like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblMyLog]
(
    [Pkid] [bigint] IDENTITY(1,1) NOT NULL,
    [JobId] [int] NOT NULL,
    [log_recorded] [datetime] NOT NULL,
    [status] [nvarchar](max) NULL
)
GO

SET IDENTITY_INSERT [dbo].[tblMyLog] ON 
GO

INSERT [dbo].[tblMyLog] ([Pkid], [JobId], [log_recorded], [status]) 
VALUES (1, 1, CAST(N'2021-06-29T15:35:09.917' AS DateTime), N'Started')
GO

INSERT [dbo].[tblMyLog] ([Pkid], [JobId], [log_recorded], [status]) 
VALUES (2, 1, CAST(N'2021-06-29T15:36:08.810' AS DateTime), N'Ended')
GO

INSERT [dbo].[tblMyLog] ([Pkid], [JobId], [log_recorded], [status]) 
VALUES (3, 2, CAST(N'2021-06-29T15:33:41.133' AS DateTime), N'Started')
GO

INSERT [dbo].[tblMyLog] ([Pkid], [JobId], [log_recorded], [status]) 
VALUES (4, 2, CAST(N'2021-06-29T15:35:09.917' AS DateTime), N'Ended')
GO

I would like to get in a format like so I do some joins to tblMyLog from another table:

JobId StartTime                EndTime
------------------------------------------------------
1     2021-06-29 15:35:09.917  2021-06-29 15:36:08.810
2     2021-06-29 15:33:41.133  2021-06-29 15:35:09.917

but I can't figure out the pivot table syntax to do this.

Answer 1

Assuming a JobID can have multiple start/end times, we can use the window function sum() over() to create an ad-hoc group Grp

Example

Select JobID
      ,StartTime = min( case when status='Started' then log_recorded end)
      ,EndTime   = max( case when status='Ended'   then log_recorded end)
 From (
        Select *
              ,Grp = sum( case when status='Started' then 1 end) over ( partition by JobID order by log_recorded) 
         From [tblMyLog]
      ) A
 Group By JobID,Grp

Results

JobID   StartTime                 EndTime
1       2021-06-29 15:35:09.917   2021-06-29 15:36:08.810
2       2021-06-29 15:33:41.133   2021-06-29 15:35:09.917