Query Recursiva

Question

Olá boa tarde pessoal,

Sou novo aqui no fórum, mas gostaria da ajuda de vocês.

Tenho a seguinte situação.

Em uma tabela tenho a hierarquia de departamentos de um funcionário que está disposto da seguinte maneira, por exemplo:

FUNC1 | ORGANIZACAO | CONSELHO 1 |  PRESIDENCIA | DIRETORIA 1 | DEPTO A

FUNC2 | ORGANIZACAO | CONSELHO 2 |  PRESIDENCIA | DIRETORIA 2 | DEPTO B

Sendo assim preciso de um query que me traga a seguinte resposta:

1  | ORGANIZACAO 

2  | ORGANIZACAO | CONSELHO 1

3  | ORGANIZACAO | CONSELHO 1 | PRESIDENCIA

4  | ORGANIZACAO | CONSELHO 1 | PRESIDENCIA | DIRETORIA 1

5  | ORGANIZACAO | CONSELHO 1 | PRESIDENCIA | DIRETORIA 1 | DEPTO A 

6  | ORGANIZACAO | CONSELHO 1 | PRESIDENCIA | DIRETORIA 2 | DEPTO B

7  | ORGANIZACAO | CONSELHO 2

8  | ORGANIZACAO | CONSELHO 2 | PRESIDENCIA

9  | ORGANIZACAO | CONSELHO 2 | PRESIDENCIA | DIRETORIA 1

10 | ORGANIZACAO | CONSELHO 2 | PRESIDENCIA | DIRETORIA 1 | DEPTO A

11 | ORGANIZACAO | CONSELHO 2 | PRESIDENCIA | DIRETORIA 2 | DEPTO B

Tentei realizar uma CTE para resolver esse problema mas infelizmente a resposta ainda está longe de chegar no resultado acima, mas se alguém puder me ajudar, agradeço.

Uma outra solução que tenho pensado é fazer um UNPIVOT dessas colunas e transformá-las em linhas com o Nome do Departamento PAI na frente, ficando:

DEPTO | DEPTO PAI

ORGANIZACAO | NULL

ORGANIZACAO | CONSELHO 1

ORGANIZACAO | CONSELHO 2 

CONSELHO 1 | PRESIDENCIA

CONSELHO 2 | PRESIDENCIA

PRESIDENCIA | DIRETORIA 1   .....  E assim sucessivamente, porque nessa estrutura a resposta da CTE funciona.

Porém, se alguém puder me ajudar na primeira situação tendo uma forma mais simples de resolver, eu continuo muito agradecido.

Att.

Answer 1

Boa tarde,

Alex, cada valor (ex: 'ORGANIZACAO', 'CONSELHO 1', 'PRESIDENCIA', 'DIRETORIA 1', 'DEPTO A') está em uma coluna separada? São 5 níveis fixos na hierarquia?

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

Olá boa tarde,

Desde já obrigado.

Para cada nível de departamento é uma coluna separada e no exemplo deu 5 níveis mas na verdade hoje são 8 níveis, que infelizmente podem ser alterados de acordo com o organograma da empresa, pois podem criar um novo departamento e com isso tenho que remontar a estrutura.

Att.

Answer 3

Segue uma sugestão para teste utilizando uma CTE recursiva:

with CTE_Rec as
(
    select
        Depto1,
        Depto2,
        Depto3,
        Depto4,
        Depto5,
        5 as Nivel
    from Tabela
    
    union all
    
    select
        Depto1,
        case when Nivel >= 2 then Depto2 end,
        case when Nivel >= 3 then Depto3 end,
        case when Nivel >= 4 then Depto4 end,
        null,
        Nivel - 1
    from CTE_Rec
    where Nivel > 0
)

select distinct
    Depto1,
    Depto2,
    Depto3,
    Depto4,
    Depto5
from CTE_Rec
order by
    Depto1,
    Depto2,
    Depto3,
    Depto4,
    Depto5

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 4

Alex_TJ_88,

Veja este exemplo extraído com base da documentação oficial do SQL Server:

CREATE TABLE tblCli (Codigo INT, Nome VARCHAR(40), CodigoSup INT)

 

INSERT INTO tblCli VALUES (1,'Presidente', NULL)

INSERT INTO tblCli VALUES (2,'Superintendente', 1)

INSERT INTO tblCli VALUES (3,'Gerente', 2)

INSERT INTO tblCli VALUES (4,'Coordenador', 3)

INSERT INTO tblCli VALUES (5,'Supervisor', 4)

INSERT INTO tblCli VALUES (6,'Analista', 5)

INSERT INTO tblCli VALUES (7,'Estagiário', 6)

INSERT INTO tblCli VALUES (8,'Conselheiro', 1)

 

WITH Rel

AS (

SELECT Nome, 1 AS Nivel, Codigo FROM tblCli

WHERE CodigoSup IS NULL

UNION ALL

SELECT tblCli.Nome, Nivel + 1, tblCli.Codigo FROM Rel

INNER JOIN tblCli ON Rel.Codigo = tblCli.CodigoSup

)

 

SELECT

REPLICATE('--',Nivel) + '> ' + Nome

FROM

Rel

ORDER BY

Nivel

Este tipo de cenário que você esta elaborando, também é denominado em alguns casos como autorelacionamento, tenho um exemplo que utilizo em minhas aulas:

-- Criando a Tabela Funcao --
Create Table Funcao
(CodigoFuncao Char(2) Primary Key Not Null,
  DescricaoFuncao Varchar(50) Not Null,
  ValorSalario Numeric(6,2) Not Null)
Go

-- Criando a Tabela Areas --
Create Table Areas
(CodigoArea Char(2) Primary Key Not Null,
 DescricaoArea Varchar(30) Not Null,
 RamalTelefone SmallInt Not Null)
Go

-- Criando a Tabela Funcionarios --
Create Table Funcionarios 
(NumeroRegistro Int Primary Key Not Null,
 NomeFuncionario Varchar(80) Not Null,
 DtAdmissao Date Default GetDate(),
 Sexo Char(1) Not Null Default 'M',
 CodigoFuncao Char(2) Not Null,
 CodigoArea Char(2) Not Null,
 CodigoDoGestor TinyInt Not Null)
Go

-- Criando os relacionamentos --
Alter Table Funcionarios
 Add Constraint [FK_Funcionarios_Funcaos] Foreign Key (CodigoFuncao)
  References Funcao(CodigoFuncao)
Go


Alter Table Funcionarios
 Add Constraint [FK_Funcionarios_Areas] Foreign Key (CodigoArea)
  References Areas(CodigoArea)
Go

-- Inserindo os Dados --
Insert Into Funcao (CodigoFuncao, DescricaoFuncao, ValorSalario)
Values ('F1', 'Aux.Vendas', 350.00), 
	('F2', 'Vigia', 400.00),
	('F3', 'Vendedor', 800.00),
	('F4', 'Aux.Cobrança', 250.00), 
	('F5', 'Gerente', 1000.00), 
	('F6', 'Diretor', 2500.00),
	('F7', 'Presidente', 2500.00)
Go

Insert Into Areas (CodigoArea,DescricaoArea,RamalTelefone)
Values ('A1', 'Assist.Técnica', 2246),
	('A2', 'Estoque', 2589),
	('A3', 'Administração', 2772),
	('A4', 'Segurança', 1810),
	('A5', 'Vendas', 2599),
	('A6', 'Cobrança', 2688)
Go

Insert Into Funcionarios (NumeroRegistro, NomeFuncionario, DtAdmissao, Sexo, CodigoArea, CodigoFuncao, CodigoDoGestor)
Values (101, 'Juca Sampaio', '2003-08-10', 'M', 'A3', 'F5',22),
	(104, 'Fernando Pereira', '2004-03-02', 'M', 'A4', 'F6',101),
	(134, 'João Alves', '2002-05-03', 'M', 'A5', 'F1',123),
	(121, 'Paulo Souza', '2001-12-10', 'M', 'A6', 'F5',115),
	(195, 'Maria Silveira', '2002-01-05', 'F','A3', 'F5',139),
	(139, 'Ana Laura', '2003-01-12', 'F', 'A4', 'F6',123),
	(123, 'Silvio Rocha', '2003-06-29', 'M', 'A6', 'F3',148),
	(148, 'Iolanda Aguiar', '2002-06-01', 'F', 'A5', 'F3',195),
	(115, 'Roberto Fernandes', '2003-10-15', 'M', 'A2', 'F2',22),
	(22, 'Sergio Noriega', '2000-02-10', 'M', 'A6', 'F6',22)
Go

Select  F.NumeroRegistro, 
			F.NomeFuncionario, 
			A.DescricaoArea 'Descricação da Área de Trabalho',
			F1.DescricaoFuncao 'Descricação da Função',
			Year(F.DtAdmissao) As Ano,
			Month(F.DtAdmissao) As Mês,
			Day(F.DtAdmissao) as Dia,
			Convert(Varchar(3),F.CodigoDoGestor) + ' - ' + F2.NomeFuncionario As 'Gestor ou Responsável'
From Funcionarios F Inner Join Areas A
								  On F.CodigoArea = A.CodigoArea
								 Inner Join Funcao F1
								  On F.CodigoFuncao = F1.CodigoFuncao
								 Inner Join Funcionarios F2
								  On F2.NumeroRegistro = F.CodigoDoGestor
Order By F.CodigoDoGestor Asc
Go

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 5

Ajudou demais e mais uma vez obrigado. Att.

Answer 6

Júnior, obrigado também pela ajuda, o autorelacionamento quando estamos tratando no Datawarehouse é um complicador a mais, mas a função replicate que você mostrou me agregou bastante para conseguir montar minha Dimensão de estrutura organizacional.

Mais uma vez obrigado e sucesso.

Att.

Answer 7

Alex_TJ_88,

Sim, sim, mas mesmo no DW é muito comum se depararmos com o auto relacionamento.

Em relação a função replicate, que bom que deu certo.

Abraços.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]