By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #A (id int, status_key int, status_date date, seq int, PRIMARY KEY (id, seq));
INSERT INTO #A (id, status_key, status_date, seq) VALUES
(10035, 2, '2020-10-01', 1),
(10035, 3, '2020-10-03', 2),
(10049, 2, '2020-06-10', 1),
(10049, 3, '2020-06-13', 2),
(10049, 4, '2020-06-17', 3),
(10049, 5, '2020-07-03', 4);
6 rows affected
CREATE TABLE #B (status_key int PRIMARY KEY, status_name nvarchar(50));
INSERT INTO #B (status_key, status_name) VALUES
(2, 'accepted'),
(3, 'conditionally accepted'),
(4, 'decided'),
(5, 'declined');
4 rows affected
WITH a1 AS
(SELECT A.ID,
A.status_key AS a1_status_key,
A.status_date AS a1_status_date,
LAG(A.status_key, 1) OVER (PARTITION BY A.id ORDER BY A.seq) AS a2_status_key,
LAG(A.status_date, 1) OVER (PARTITION BY A.id ORDER BY A.seq) AS a2_status_date,
LEAD(A.status_key, 1) OVER (PARTITION BY A.id ORDER BY A.seq) AS a3_status_key,
LEAD(A.status_date, 1) OVER (PARTITION BY A.id ORDER BY A.seq) AS a3_status_date,
LEAD(A.status_key, 2) OVER (PARTITION BY A.id ORDER BY A.seq) AS a4_status_key,
LEAD(A.status_date, 2) OVER (PARTITION BY A.id ORDER BY A.seq) AS a4_status_date
FROM #A A
)
SELECT a1.id,
a1.a1_status_key as current_status_key,
b1.status_name as current_status_name,
a1.a1_status_date as current_status_date,
a1.a2_status_key as previous_status_key,
b2.status_name as previous_status_name,
a1.a2_status_date as previous_status_date,
a1.a3_status_key as next_status_key,
b3.status_name as next_status_name,
a1.a3_status_date as next_status_date,
a1.a4_status_key as next_2_status_key,
b4.status_name as next_2_status_name,
a1.a4_status_date as next_2_status_date
FROM a1
LEFT JOIN #B b1 ON a1.a1_status_key = b1.status_key
LEFT JOIN #B b2 ON a1.a2_status_key = b2.status_key
LEFT JOIN #B b3 ON a1.a3_status_key = b3.status_key
LEFT JOIN #B b4 ON a1.a4_status_key = b4.status_key;
| id | current_status_key | current_status_name | current_status_date | previous_status_key | previous_status_name | previous_status_date | next_status_key | next_status_name | next_status_date | next_2_status_key | next_2_status_name | next_2_status_date |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 10035 | 2 | accepted | 2020-10-01 | null | null | null | 3 | conditionally accepted | 2020-10-03 | null | null | null |
| 10035 | 3 | conditionally accepted | 2020-10-03 | 2 | accepted | 2020-10-01 | null | null | null | null | null | null |
| 10049 | 2 | accepted | 2020-06-10 | null | null | null | 3 | conditionally accepted | 2020-06-13 | 4 | decided | 2020-06-17 |
| 10049 | 3 | conditionally accepted | 2020-06-13 | 2 | accepted | 2020-06-10 | 4 | decided | 2020-06-17 | 5 | declined | 2020-07-03 |
| 10049 | 4 | decided | 2020-06-17 | 3 | conditionally accepted | 2020-06-13 | 5 | declined | 2020-07-03 | null | null | null |
| 10049 | 5 | declined | 2020-07-03 | 4 | decided | 2020-06-17 | null | null | null | null | null | null |