SQL, group multiple rows into 2 rows and make one row

I have such a table

    Id   |    Name     |         Date    
    1         John         2020-10-24 23:51:00.000  
    1         John         2020-10-25 08:00:00.000        
    2         Edward       2020-10-24 23:55:00.000          
    2         Edward       2020-10-25 08:02:00.000           
    2         Edward       2020-10-25 15:50:00.000          
    2         Edward       2020-10-26 00:02:00.000
    3         Mark         2020-10-25 08:05:00.000
    3         Mark         2020-10-25 15:53:00.000

       

Dates are subject to change, I want to group them in two rows. Dates range may be a maximum of 15 hours.

My expected output is

 Id            |  Name     |       StartDate                 |    EndDate
 1               John         2020-10-24 23:51:00.000        2020-10-25 08:00:00.000
 2               Edward       2020-10-24 23:55:00.000        2020-10-25 08:02:00.000
 2               Edward       2020-10-25 15:50:00.000        2020-10-26 00:02:00.000
 3               Mark         2020-10-25 08:05:00.000        2020-10-25 15:53:00.000

Answer

Assuming every start date always has a matching end date, we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date) rn
    FROM yourTable
)

SELECT
    Id,
    Name,
    MAX(CASE WHEN (rn - 1) % 2 = 0 THEN Date END) AS StartDate,
    MAX(CASE WHEN (rn - 1) % 2 = 1 THEN Date END) AS EndDate
FROM cte
GROUP BY
    Id,
    Name,
    (rn - 1) / 2;

Here we are using pivoting logic along with the modulus of ROW_NUMBER to figure out which date records comes “first” and corresponds to the start date, and why comes “last” and corresponds to the end date.