SQL Server 2019

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

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
 Jul 25 2020 11:26:55 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE Employee (
  EmployeeId INTEGER,
  Name VARCHAR(4),
  DeptId INTEGER,
  IsDeleted INTEGER DEFAULT 0
);

INSERT INTO Employee
  (EmployeeId, Name, DeptId)
VALUES
  ('100', 'John', '100'),
  ('101', 'Kery', '100'),
  ('102', 'Tiny', '100');

CREATE TABLE Teams (
  TeamId INTEGER,
  TeamLeaderId INTEGER,
  TeamName VARCHAR(12),
  IsDeleted INTEGER DEFAULT 0
);

INSERT INTO Teams
  (TeamId, TeamLeaderId, TeamName)
VALUES
  ('1', '100', 'Finance-2021');

CREATE TABLE TeamMember (
  TeamMemberId INTEGER,
  TeamId INTEGER,
  EmployeeId INTEGER,
  IsDeleted INTEGER DEFAULT 0
);

INSERT INTO TeamMember
  (TeamMemberId, TeamId, EmployeeId)
VALUES
  ('1', '1', '101');
5 rows affected

SELECT
  t.TeamId,
  t.TeamName,
  m.EmployeeIdentity,
  e.EmployeeId,
  e.Name AS EmployeeName
FROM Teams t
CROSS APPLY (
    SELECT
      t.TeamLeaderId AS EmployeeId,
      'Team Leader' AS EmployeeIdentity
    UNION ALL
    SELECT
      tm.EmployeeId,
      'Team Member'
    FROM TeamMember tm
    WHERE tm.TeamId = t.TeamId
) m
JOIN Employee e ON e.EmployeeId = m.EmployeeId
  AND e.IsDeleted = 0
WHERE t.TeamId = 1;


TeamId
TeamName
EmployeeIdentity
EmployeeId
EmployeeName


1
Finance-2021
Team Leader
100
John

1
Finance-2021
Team Member
101
Kery

TeamId	TeamName	EmployeeIdentity	EmployeeId	EmployeeName
1	Finance-2021	Team Leader	100	John
1	Finance-2021	Team Member	101	Kery