SQL Server 2019

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

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