SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE PersonWorkload
(
WorkloadID INT PRIMARY KEY IDENTITY,
Post VARCHAR(30),
Period VARCHAR(12),
PersonID INT,
GroupID INT,
Notes VARCHAR(100)
)

INSERT INTO PersonWorkload (Post, Period, PersonID, GroupID, Notes)
    VALUES 
    ('Group Leader', 'Q1', 1, 1, NULL),
    ('Group Leader', 'Q2', 1, 1, NULL),
    ('Group Leader', 'Q3', 1, 2, NULL),
    ('Group Leader', 'Q4', 1, 2, NULL),
    ('Security', 'Q1', 3, 3, NULL),
    ('Security', 'Q2', 3, 3, NULL),
    ('Security', 'Q3', 4, 4, NULL),
    ('Security', 'Q4', 4, 4, NULL),
    ('Team member', 'Q1', 7, 5, NULL),
    ('Team member', 'Q2', 7, 5, 'Leaving programme this qtr'),
    ('Team member', 'Q3', 13, 6, NULL),
    ('Team member', 'Q4', 13, 6, NULL),
    ('Team member', 'Q1', 8, 7, NULL),
    ('Team member', 'Q2', 8, 7, NULL),
    ('Team member', 'Q3', 8, 7, NULL),
    ('Team member', 'Q4', 8, 7, NULL),
    ('Team member','Q1',9,8,'Temp posting'),
    ('Team member', 'Q2', 10, 9, NULL),
    ('Team member', 'Q3', 10, 9, NULL),
    ('Team member', 'Q4', 10, 9, NULL),
    ('Comms','Q2',11,10,NULL),
    ('Comms','Q3',11,10,NULL),
    ('Comms','Q4',11,10,NULL)

23 rows affected

Post	rn	Q1	Q2	Q3	Q4
Comms	1	null	11	11	11
Group Leader	1	1	1	1	1
Security	1	3	3	4	4
Team member	1	7	7	13	13
Team member	2	8	8	8	8
Team member	3	9	10	10	10