By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Department (ID INT, NAME VARCHAR(100))
CREATE TABLE Team (ID INT, NAME VARCHAR(100), DEPARTMENT INT)
CREATE TABLE Task (ID INT, NAME VARCHAR(100), TEAM VARCHAR(100))
INSERT INTO Department (ID, NAME)
VALUES (12345, 'IT'),
(25695, 'HR'),
(89685, 'PAY'),
(47896, 'OPS')
INSERT INTO Team (ID, NAME, DEPARTMENT)
VALUES (34252, 'TEAM1', 12345),
(54234, 'TEAM2', 12345),
(52385, 'TEAM1', NULL),
(91231, 'TEAM1', 89685)
INSERT INTO Task (ID, NAME, Team)
VALUES (1, 'Ta', '34252,54234,52385'),
(2, 'Tb', '52385,91231'),
(3, 'Tc', NULL),
(4, 'Td', '34252')
12 rows affected
SELECT d.ID AS DepID
, d.NAME AS Dep
, t.ID AS TeamID
, t.NAME AS Team
, tsk.TaskID
, tsk.Task
FROM
(
SELECT tsk.ID AS TaskID
, tsk.Name AS Task
, ss.[value] AS TeamID
FROM Task tsk
CROSS APPLY STRING_SPLIT(tsk.Team, ',') ss
) tsk
LEFT JOIN Team t ON t.ID = tsk.TeamID
LEFT JOIN Department d ON d.ID = t.DEPARTMENT
| DepID | Dep | TeamID | Team | TaskID | Task |
|---|---|---|---|---|---|
| 12345 | IT | 34252 | TEAM1 | 1 | Ta |
| 12345 | IT | 54234 | TEAM2 | 1 | Ta |
| null | null | 52385 | TEAM1 | 1 | Ta |
| null | null | 52385 | TEAM1 | 2 | Tb |
| 89685 | PAY | 91231 | TEAM1 | 2 | Tb |
| 12345 | IT | 34252 | TEAM1 | 4 | Td |