By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 1 AS ControlID, 'ABC' AS Role FROM dual UNION ALL
SELECT 1, 'SJD' FROM dual UNION ALL
SELECT 1, 'DFK' FROM dual UNION ALL
SELECT 2, 'LLL' FROM dual UNION ALL
SELECT 2, 'IJK' FROM dual
)
SELECT
CASE WHEN ROW_NUMBER() OVER (PARTITION BY ControlID ORDER BY Role) = 1
THEN ControlID ELSE NULL END AS ControlID,
t.Role
FROM yourTable t
ORDER BY
t.ControlID,
t.Role;
| CONTROLID | ROLE |
|---|---|
| 1 | ABC |
| null | DFK |
| null | SJD |
| 2 | IJK |
| null | LLL |