What would be the proper method to approach this assignment to get the Date in the proper arranged or consecutive manner in Microsoft SQL server

first the database is a normal single line one but we have to query to get it to the assignment part while creating :

the assignment is to arrange the tasks provided with dates in such format to create a schedule for workflow
the motive is to arrange them in kind of this format

I have absolutely no clue but have tried couple things with group by and all open to any answers

sorry but new here please help thank you!`

CREATE DATABASE project ;
CREATE TABLE project_phases(
project_id int,
phase varchar(200),
start_date date
);

INSERT INTO project_phases (project_id,phase,start_date)
VALUES (1, 'design', '2021-01-01');

INSERT INTO project_phases (project_id,phase,start_date)
VALUES (1, 'development', '2021-01-02');

INSERT INTO project_phases (project_id,phase,start_date)
VALUES (1, 'deployment', '2021-01-03');`

Solution

Seems like the window function lead() over() is a good fit here

Select project_id
      ,from_phase   = phase
      ,to_phase     = lead(phase,1) over (partition by project_id order by start_date)
      ,start_date
      ,end_date     = lead(start_date,1) over (partition by project_id order by start_date)
from project_phases

Results