SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE EmployeeJobs (
    EMPLOYEE_ID INT,
    START_DATE DATE,
    END_DATE DATE NULL,
    JOB_TITLE VARCHAR(50)
);

INSERT INTO EmployeeJobs (EMPLOYEE_ID, START_DATE, END_DATE, JOB_TITLE)
VALUES 
(1442, '2024-07-30', NULL, 'Tutor'),
(1442, '2024-07-30', NULL, 'Tutor'),
(1442, '2024-06-28', NULL, 'Instructional Specialist'),
(1442, '2024-05-01', '2024-06-27', 'Instructional Specialist'),
(1442, '2021-12-16', '2024-07-29', 'Tutor'),
(1442, '2021-12-16', NULL, 'Lead Instructor'),
(1442, '2021-12-16', '2024-07-29', 'Tutor');
7 rows affected

WITH NullEndDateFlag AS
(
    SELECT DISTINCT
        *,
        MAX
        (
            CASE
                WHEN END_DATE IS NULL
                THEN 1
                ELSE 0
            END
        ) OVER
        (
            PARTITION BY EMPLOYEE_ID
        ) AS HAS_NULL_END_DATE
    FROM EmployeeJobs
),
RowNums AS
(
    SELECT
        EMPLOYEE_ID,
        JOB_TITLE,
        ROW_NUMBER() OVER
        (
            PARTITION BY EMPLOYEE_ID
            ORDER BY START_DATE DESC
        ) AS ROW_NUM
    FROM NullEndDateFlag
    WHERE END_DATE IS NULL
    OR HAS_NULL_END_DATE = 0
)
SELECT *
FROM RowNums
PIVOT
(
    MAX(JOB_TITLE)
    FOR ROW_NUM IN
    (
        [1],
        [2],
        [3],
        [4],
        [5]
    )
) t


EMPLOYEE_ID
1
2
3
4
5


1442
Tutor
Instructional Specialist
Lead Instructor
null
null


DELETE
FROM EmployeeJobs
WHERE END_DATE IS NULL;
4 rows affected

SELECT *
FROM EmployeeJobs;


EMPLOYEE_ID
START_DATE
END_DATE
JOB_TITLE


1442
2024-05-01
2024-06-27
Instructional Specialist

1442
2021-12-16
2024-07-29
Tutor

1442
2021-12-16
2024-07-29
Tutor


WITH NullEndDateFlag AS
(
    SELECT DISTINCT
        *,
        MAX
        (
            CASE
                WHEN END_DATE IS NULL
                THEN 1
                ELSE 0
            END
        ) OVER
        (
            PARTITION BY EMPLOYEE_ID
        ) AS HAS_NULL_END_DATE
    FROM EmployeeJobs
),
RowNums AS
(
    SELECT
        EMPLOYEE_ID,
        JOB_TITLE,
        ROW_NUMBER() OVER
        (
            PARTITION BY EMPLOYEE_ID
            ORDER BY START_DATE DESC
        ) AS ROW_NUM
    FROM NullEndDateFlag
    WHERE END_DATE IS NULL
    OR HAS_NULL_END_DATE = 0
)
SELECT *
FROM RowNums
PIVOT
(
    MAX(JOB_TITLE)
    FOR ROW_NUM IN
    (
        [1],
        [2],
        [3],
        [4],
        [5]
    )
) t


EMPLOYEE_ID
1
2
3
4
5


1442
Instructional Specialist
Tutor
null
null
null
EMPLOYEE_ID	START_DATE	END_DATE	JOB_TITLE
1442	2024-05-01	2024-06-27	Instructional Specialist
1442	2021-12-16	2024-07-29	Tutor
1442	2021-12-16	2024-07-29	Tutor