none
Query Recursiva RRS feed

  • Pergunta

  • 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.

    • Editado Alex_TJ_88 sexta-feira, 29 de março de 2019 17:44
    sexta-feira, 29 de março de 2019 17:35

Respostas

  • 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

    sexta-feira, 29 de março de 2019 20:23
  • 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]

    • Marcado como Resposta Alex_TJ_88 terça-feira, 2 de abril de 2019 19:18
    sábado, 30 de março de 2019 13:41

Todas as Respostas

  • 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

    sexta-feira, 29 de março de 2019 17:55
  • 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.

    sexta-feira, 29 de março de 2019 18:34
  • 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

    sexta-feira, 29 de março de 2019 20:23
  • 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]

    • Marcado como Resposta Alex_TJ_88 terça-feira, 2 de abril de 2019 19:18
    sábado, 30 de março de 2019 13:41
  • Ajudou demais e mais uma vez obrigado. Att.
    • Marcado como Resposta Alex_TJ_88 terça-feira, 2 de abril de 2019 19:18
    • Não Marcado como Resposta Alex_TJ_88 terça-feira, 2 de abril de 2019 19:18
    terça-feira, 2 de abril de 2019 19:18
  • 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.

    terça-feira, 2 de abril de 2019 19:22
  • 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]

    terça-feira, 2 de abril de 2019 19:47