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-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE [dbo].[sampleusertable]
(
    [UserID] [varchar](50) NOT NULL,
    [Useremail] [varchar](50) NULL,
    [ManagerUserID] [varchar](50) NULL,

    CONSTRAINT [PK_sampleusertable] 
        PRIMARY KEY CLUSTERED ([UserID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[sampleusertable] ([UserID], [Useremail], [ManagerUserID]) VALUES 
(N'1', N'one@one.com', N'2'),
(N'2', N'two@manager.com', N'3'),
(N'3', N'three@boss.com', NULL);
3 rows affected

SELECT * FROM sampleusertable


UserID
Useremail
ManagerUserID


1
one@one.com
2

2
two@manager.com
3

3
three@boss.com
null


SELECT 
  a.UserID, 
  a.Useremail, 
  a.ManagerUserID, 
  (SELECT Useremail FROM sampleusertable WHERE UserID = a.ManagerUserID) AS ManagerEmail
FROM sampleusertable a


UserID
Useremail
ManagerUserID
ManagerEmail


1
one@one.com
2
two@manager.com

2
two@manager.com
3
three@boss.com

3
three@boss.com
null
null


SELECT 
  a.UserID, 
  a.Useremail, 
  COALESCE(a.ManagerUserID, UserID) AS ManagerUserID, 
  COALESCE((SELECT Useremail FROM sampleusertable WHERE UserID = a.ManagerUserID), Useremail) AS ManagerEmail
FROM sampleusertable a


UserID
Useremail
ManagerUserID
ManagerEmail


1
one@one.com
2
two@manager.com

2
two@manager.com
3
three@boss.com

3
three@boss.com
3
three@boss.com


SELECT a.UserID,
  a.Useremail,
  a.ManagerUserID,
  b.Useremail AS ManagerEmail
FROM sampleusertable a
LEFT JOIN sampleusertable b ON a.ManagerUserID = b.UserID


UserID
Useremail
ManagerUserID
ManagerEmail


1
one@one.com
2
two@manager.com

2
two@manager.com
3
three@boss.com

3
three@boss.com
null
null


SELECT a.UserID,
  a.Useremail,
  COALESCE(a.ManagerUserID, a.UserID) AS ManagerUserID,
  COALESCE(b.Useremail, a.Useremail) AS ManagerEmail
FROM sampleusertable a
LEFT JOIN sampleusertable b ON a.ManagerUserID = b.UserID


UserID
Useremail
ManagerUserID
ManagerEmail


1
one@one.com
2
two@manager.com

2
two@manager.com
3
three@boss.com

3
three@boss.com
3
three@boss.com
UserID	Useremail	ManagerUserID	ManagerEmail
1	one@one.com	2	two@manager.com
2	two@manager.com	3	three@boss.com
3	three@boss.com	null	null