How to select records in a table Auto referenced using Recursion?

Navigation
3

In a scenario of areas where one area can be overseen by another one is represented in a tree structure as follows:

Problem:TheneedtoselectÁrea(CBT-CubatãoIndustrialComplex)followtheHierarchy.(InthissituationalltheAreaslistedintheimage)

TherewillbesituationswheretheÁreas(CBT-InfrastructureManagement)mayberequested,sothiswouldbetheresult:

  • How should Área be to select this data?

Table Areas: 

CREATE TABLE [dbo].[Areas](
 [IdArea] [int] IDENTITY(1,1) NOT NULL,
 [IdAreaPai] [int] NULL,
 [Nome] [varchar](50) NOT NULL)

Inserts: 

insert into Areas(IdAreaPai, Nome)
values(null,'CBT - Complexo Industrial de Cubatão')


insert into Areas(IdAreaPai, Nome)
values(1,'CBT - Áreas Corporativas')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Faturamento')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Tecnologia da Informação')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Controladoria')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Infraestrutura')

insert into Areas(IdAreaPai, Nome)
values(6,'CBT - Restaurante')

insert into Areas(IdAreaPai, Nome)
values(6,'CBT - Serviço de infraestrutura')

insert into Areas(IdAreaPai, Nome)
values(6,'CBT - Transporte')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Proj. Correntes Nitrogenados')

insert into Areas(IdAreaPai, Nome)
values(10,'CBT - Arquivo Técnico')

insert into Areas(IdAreaPai, Nome)
values(10,'CBT - Proj. Correntes')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Recursos Humanos')

 insert into Areas(IdAreaPai, Nome)
 values(null,'CBT - Complexo')
    
asked by anonymous 27.06.2017 / 16:14

2 answers

2

Through the questions:

I was able to get to the select below. In my case the stopping point will be Área. itself 

  WITH hierarquia AS
      ( SELECT *
       FROM Areas
       WHERE IdArea = 6
         UNION ALL
         SELECT t.*
         FROM Areas t
         INNER JOIN hierarquia s ON t.IdAreaPai = s.IdArea )
    SELECT *
    FROM hierarquia

Result:

    
27.06.2017 / 18:29
0

See if this is what you need ... 

WITH ArvoreAreas AS 
( 
    SELECT 
        IdArea
        ,IdAreaPai
        ,1 as Level
        ,CAST(Nome as varchar(max)) as Nodes 
        ,IdArea as IdentificadorUnico
    FROM 
        Areas 
    WHERE 
        IdAreaPai is null

    UNION ALL

    SELECT 
        c.IdArea
        ,c.IdAreaPai
        ,Level + 1
        ,Cast(ac.Nodes + '->' + c.Nome as varchar(max))
        ,c.IdAreaPai as IdentificadorUnico
    FROM 
        Areas c 
        INNER JOIN ArvoreAreas ac ON c.IdAreaPai = ac.IdArea
)
SELECT * FROM ArvoreAreas 
WHERE Nodes like 'CBT - Complexo Industrial de Cubatão%'
ORDER BY Nodes
    
27.06.2017 / 16:55
Why is the ZipFile function not working? Advantages of Watch in comparison to Break point