Preciso gerar uma lista de usuários dos serviços de códigos. Se um usuário não tiver o código de obter o seu gerenciador de código, e assim por diante até a hierarquia.
A tabela inicial se parece com isso:
manager emp code
-----------------------
boss subboss AAA
boss subsub SUBCODE
subboss john ABC
subboss alan (null)
(null) boss ZZZ
subsub steve (null)
steve rick (null)
CREATE TABLE Users
(
[manager] varchar(10),
[emp] varchar(10),
[code] varchar(10)
);
INSERT INTO Users ([manager], [emp], [code])
VALUES
('boss', 'subboss', 'AQQ'),
('boss', 'subsub', 'SUBSUB'),
('subboss', 'john', 'ABC'),
('subboss', 'alan', null),
(null, 'boss', 'ZZZ'),
('subsub', 'steve', null),
('steve', 'rick', null);
Resultado desejado é este:
manager emp code
------------------------
boss subboss AAA
boss subsub SUBCODE
subboss john ABC
subboss alan AAA
(null) boss ZZZ
subsub steve SUBCODE
steve rick SUBCODE
Minha primeira tentativa:
select
manager, emp,
coalesce(code, (select code from Users u1 where u.manager = u1.code))
from
Users u;
Mas ele retorna somente o gestor direto de código.
Eu gostaria de dicas sobre como fazê-lo de forma recursiva com CTE.