By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE vwPatientPEIOutcomes (
patientname varchar(max) NOT NULL,
intervalname varchar(max) NOT NULL,
) ;
INSERT INTO vwpatientPEIOutcomes (patientname,intervalname)
VALUES
('Patient 1','End'),
('Patient 2','Start'),
('Patient 3','Start'),
('Patient 4','End'),
('Patient 4','6-Month'),
('Patient 4','Start'),
('Patient 5','6-Month'),
('Patient 6','Start'),
('Patient 6','End'),
('Patient 7','6-Month');
10 rows affected
SELECT patientname,
MAX(CASE WHEN rn = 1 THEN intervalname END) AS intervalname1,
MAX(CASE WHEN rn = 2 THEN intervalname END) AS intervalname2,
MAX(CASE WHEN rn = 3 THEN intervalname END) AS intervalname3
FROM
( SELECT *, ROW_NUMBER() OVER ( PARTITION BY patientname
ORDER BY patientname) AS rn
FROM vwPatientPEIOutcomes ) AS p
GROUP BY patientname
ORDER BY patientname
| patientname | intervalname1 | intervalname2 | intervalname3 |
|---|---|---|---|
| Patient 1 | End | null | null |
| Patient 2 | Start | null | null |
| Patient 3 | Start | null | null |
| Patient 4 | End | 6-Month | Start |
| Patient 5 | 6-Month | null | null |
| Patient 6 | Start | End | null |
| Patient 7 | 6-Month | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.