none
Como obtener registros de una tabla jerarquica especificandole un valor de jerarquia maximo

    Question

  • Hola a todos como estan... Tengo una tabla jerarquica de categorias, algo asi...

    CREATE TABLE dbo.Categorias
    (
     CategoriaId int identity(1, 1) primary key clustered,
     Categoria varchar(20),
     CategoriaPadreId int foreign key references dbo.Categorias (CategoriaId)
    )
    INSERT INTO Categorias (Categoria, CategoriaPadreId8
       ('Software' null),
       ('Hardware', null),
       ('Visual Studio' 1),
       ('2005' 3),
       ('2008', 3),
       ('Version standar', 5),
       ('Version professional', 5)
       
    Necesito una forma de obtener las categorias pero hasta un nivel de jerarquia de un entero que le pase... Me explico "aunque no se si esta bien dicho, se los dire como yo lo veo"
    Software = A un padre el cual "por lo que se puede decir que su nivel de jerarquia es 1"
    Visual Studio = Hijo de padre "por lo que pienso que su nivel de jerarquia es 2"
    2005 y 2008 son hijos de visual studio por lo que su nivel de jerarquia es 3...
    Version standar y Proffesional son hijos de 2005 y 2008 su nivel de jerarquia es 4...

    Necesito una consulta que me retorne como maximo de jerarquia de N niveles... Es decir si le paso un valor numero "3" en este caso me deberia pasar hasta "2005 y 2008" ya que Las versiones standar y profeesional estan en un nivel 4...

    Es posible hacer eso? Gracias a todos

    • Edited by Pro2e Wednesday, February 01, 2012 11:24 PM
    Wednesday, February 01, 2012 11:23 PM

Answers

  • Adiciona una columna calculada a la CTE recursiva, que usas para recorrer la jerarquia, donde llevas el control del nivel. Comienzas por el valor 1 para el / los padres seleccionados en la parte ancla de la CTE, y luego incrementas ese valor por 1 en cada pase de la parte recursiva. Para parar la parte recursiva, agrega la clausula WHERE indagando por el valor del nivel una vez incrementado, y este debe ser menor o igual al que buscas.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.Categorias
    (
    CategoriaId int identity(1, 1) primary key clustered,
    Categoria varchar(20),
    CategoriaPadreId int NULL foreign key references dbo.Categorias (CategoriaId)
    );
    
    INSERT INTO Categorias (Categoria, CategoriaPadreId)
    VALUES
    	('Software', null),
    	('Hardware', null),
    	('Visual Studio', 1),
    	('2005', 3),
    	('2008', 3),
    	('Version standar', 5),
    	('Version professional', 5);
    GO
    DECLARE @lvl int = 3;
    DECLARE @CategoriaId int = 1; -- Software 
    
    WITH Tree AS (
    SELECT
    	CategoriaId,
        Categoria,
        CategoriaPadreId,
    	1 AS lvl,
    	CONVERT(varchar(900), '/' + CONVERT(varchar(10), CategoriaId) + '/') AS mat_path
    FROM
    	Categorias
    WHERE
    	CategoriaId = @CategoriaId
    
    UNION ALL
    
    SELECT
    	C.CategoriaId,
    	C.Categoria,
    	C.CategoriaPadreId,
    	P.lvl + 1,
    	CONVERT(varchar(900), P.mat_path + CONVERT(varchar(10), C.CategoriaId) + '/')
    FROM
    	Tree AS P
    	INNER JOIN
    	dbo.Categorias AS C
    	ON P.CategoriaId = C.CategoriaPadreId
    WHERE
    	P.lvl + 1 <= @lvl
    )
    SELECT
    	*
    FROM
    	Tree
    ORDER BY
    	mat_path;
    GO
    DROP TABLE Categorias;
    GO
    

    P.S. Gracías por adjuntar el esquema de la tabla, data de ejemplo, y resultados esperados.

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Pro2e Thursday, February 02, 2012 4:13 PM
    Thursday, February 02, 2012 12:52 AM
  • Adiciona una columna calculada a la CTE recursiva, que usas para recorrer la jerarquia, donde llevas el control del nivel. Comienzas por el valor 1 para el / los padres seleccionados en la parte ancla de la CTE, y luego incrementas ese valor por 1 en cada pase de la parte recursiva. Para parar la parte recursiva, agrega la clausula WHERE indagando por el valor del nivel una vez incrementado, y este debe ser menor o igual al que buscas.

     

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.Categorias
    (
    CategoriaId int identity(1, 1) primary key clustered,
    Categoria varchar(20),
    CategoriaPadreId int NULL foreign key references dbo.Categorias (CategoriaId)
    );
    
    INSERT INTO Categorias (Categoria, CategoriaPadreId)
    VALUES
    	('Software', null),
    	('Hardware', null),
    	('Visual Studio', 1),
    	('2005', 3),
    	('2008', 3),
    	('Version standar', 5),
    	('Version professional', 5);
    GO
    DECLARE @lvl int = 3;
    DECLARE @CategoriaId int = 1; -- Software 
    
    WITH Tree AS (
    SELECT
    	CategoriaId,
        Categoria,
        CategoriaPadreId,
    	1 AS lvl,
    	CONVERT(varchar(900), '/' + CONVERT(varchar(10), CategoriaId) + '/') AS mat_path
    FROM
    	Categorias
    WHERE
    	CategoriaId = @CategoriaId
    
    UNION ALL
    
    SELECT
    	C.CategoriaId,
    	C.Categoria,
    	C.CategoriaPadreId,
    	P.lvl + 1,
    	CONVERT(varchar(900), P.mat_path + CONVERT(varchar(10), C.CategoriaId) + '/')
    FROM
    	Tree AS P
    	INNER JOIN
    	dbo.Categorias AS C
    	ON P.CategoriaId = C.CategoriaPadreId
    WHERE
    	P.lvl + 1 <= @lvl
    )
    SELECT
    	*
    FROM
    	Tree
    ORDER BY
    	mat_path;
    GO
    DROP TABLE Categorias;
    GO
    

    P.S. Gracías por adjuntar el esquema de la tabla, data de ejemplo, y resultados esperados.

     

     


    AMB

    Some guidelines for posting questions...


    Hola que tal, bueno la verdad no me funciono, no traiga lada que era. Pero me diste una idea de hacerlo asi con CTE y intentando un poco me salio... Quedo asi...

    WITH Tree AS (
      SELECT CategoriaId, Categoria, Descripcion, CategoriaPadreId, 1 AS nivel
      FROM dbo.Categorias WHERE CategoriaPadreId IS NULL
      UNION ALL
      SELECT C.CategoriaId, C.Categoria, C.Descripcion, C.CategoriaPadreId, P.nivel + 1
      FROM Tree AS P INNER JOIN dbo.Categorias AS C ON P.CategoriaId = C.CategoriaPadreId
     )
     SELECT CategoriaId, Categoria, Descripcion, CategoriaPadreId, nivel FROM Tree WHERE nivel <= @lbl ORDER BY Categoria

    Gracias una vez mas por toda tu ayuda!!!

    • Marked as answer by Pro2e Thursday, February 02, 2012 4:13 PM
    Thursday, February 02, 2012 4:13 PM

All replies

  • Adiciona una columna calculada a la CTE recursiva, que usas para recorrer la jerarquia, donde llevas el control del nivel. Comienzas por el valor 1 para el / los padres seleccionados en la parte ancla de la CTE, y luego incrementas ese valor por 1 en cada pase de la parte recursiva. Para parar la parte recursiva, agrega la clausula WHERE indagando por el valor del nivel una vez incrementado, y este debe ser menor o igual al que buscas.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.Categorias
    (
    CategoriaId int identity(1, 1) primary key clustered,
    Categoria varchar(20),
    CategoriaPadreId int NULL foreign key references dbo.Categorias (CategoriaId)
    );
    
    INSERT INTO Categorias (Categoria, CategoriaPadreId)
    VALUES
    	('Software', null),
    	('Hardware', null),
    	('Visual Studio', 1),
    	('2005', 3),
    	('2008', 3),
    	('Version standar', 5),
    	('Version professional', 5);
    GO
    DECLARE @lvl int = 3;
    DECLARE @CategoriaId int = 1; -- Software 
    
    WITH Tree AS (
    SELECT
    	CategoriaId,
        Categoria,
        CategoriaPadreId,
    	1 AS lvl,
    	CONVERT(varchar(900), '/' + CONVERT(varchar(10), CategoriaId) + '/') AS mat_path
    FROM
    	Categorias
    WHERE
    	CategoriaId = @CategoriaId
    
    UNION ALL
    
    SELECT
    	C.CategoriaId,
    	C.Categoria,
    	C.CategoriaPadreId,
    	P.lvl + 1,
    	CONVERT(varchar(900), P.mat_path + CONVERT(varchar(10), C.CategoriaId) + '/')
    FROM
    	Tree AS P
    	INNER JOIN
    	dbo.Categorias AS C
    	ON P.CategoriaId = C.CategoriaPadreId
    WHERE
    	P.lvl + 1 <= @lvl
    )
    SELECT
    	*
    FROM
    	Tree
    ORDER BY
    	mat_path;
    GO
    DROP TABLE Categorias;
    GO
    

    P.S. Gracías por adjuntar el esquema de la tabla, data de ejemplo, y resultados esperados.

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Pro2e Thursday, February 02, 2012 4:13 PM
    Thursday, February 02, 2012 12:52 AM
  • Por lo que dice aqui

    ---

    Pro2e dice,

    Necesito una forma de obtener las categorias pero hasta un nivel de jerarquia de un entero que le pase... Me explico "aunque no se si esta bien dicho, se los dire como yo lo veo"

    y

    Necesito una consulta que me retorne como maximo de jerarquia de N niveles... Es decir si le paso un valor numero "3" en este caso me deberia pasar hasta "2005 y 2008" ya que Las versiones standar y profeesional estan en un nivel 4

    ---

    Con WHERE (Transact-SQL), y Subconsultas puedes resolver el problema, ejemplo

    --use [TableName]
    --SELECT DISTINCT Categoria from dbo.Categorias
    --WHERE CategoriaPadreId= 3
    
    USE [TableName];
    GO
    SELECT DISTINCT Categoria 
    FROM dbo.Categorias 
    WHERE CategoriaPadreId IN
        (SELECT CategoriaPadreId
        FROM [AnotherTableName]
        WHERE ID = ID) ;
    GO

     


    Angel R. Jimenez G.
    Software Development
    Santo Domingo
    Republica Dominicana
    My Blog
    Thursday, February 02, 2012 1:37 AM
  • Adiciona una columna calculada a la CTE recursiva, que usas para recorrer la jerarquia, donde llevas el control del nivel. Comienzas por el valor 1 para el / los padres seleccionados en la parte ancla de la CTE, y luego incrementas ese valor por 1 en cada pase de la parte recursiva. Para parar la parte recursiva, agrega la clausula WHERE indagando por el valor del nivel una vez incrementado, y este debe ser menor o igual al que buscas.

     

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.Categorias
    (
    CategoriaId int identity(1, 1) primary key clustered,
    Categoria varchar(20),
    CategoriaPadreId int NULL foreign key references dbo.Categorias (CategoriaId)
    );
    
    INSERT INTO Categorias (Categoria, CategoriaPadreId)
    VALUES
    	('Software', null),
    	('Hardware', null),
    	('Visual Studio', 1),
    	('2005', 3),
    	('2008', 3),
    	('Version standar', 5),
    	('Version professional', 5);
    GO
    DECLARE @lvl int = 3;
    DECLARE @CategoriaId int = 1; -- Software 
    
    WITH Tree AS (
    SELECT
    	CategoriaId,
        Categoria,
        CategoriaPadreId,
    	1 AS lvl,
    	CONVERT(varchar(900), '/' + CONVERT(varchar(10), CategoriaId) + '/') AS mat_path
    FROM
    	Categorias
    WHERE
    	CategoriaId = @CategoriaId
    
    UNION ALL
    
    SELECT
    	C.CategoriaId,
    	C.Categoria,
    	C.CategoriaPadreId,
    	P.lvl + 1,
    	CONVERT(varchar(900), P.mat_path + CONVERT(varchar(10), C.CategoriaId) + '/')
    FROM
    	Tree AS P
    	INNER JOIN
    	dbo.Categorias AS C
    	ON P.CategoriaId = C.CategoriaPadreId
    WHERE
    	P.lvl + 1 <= @lvl
    )
    SELECT
    	*
    FROM
    	Tree
    ORDER BY
    	mat_path;
    GO
    DROP TABLE Categorias;
    GO
    

    P.S. Gracías por adjuntar el esquema de la tabla, data de ejemplo, y resultados esperados.

     

     


    AMB

    Some guidelines for posting questions...


    Hola que tal, bueno la verdad no me funciono, no traiga lada que era. Pero me diste una idea de hacerlo asi con CTE y intentando un poco me salio... Quedo asi...

    WITH Tree AS (
      SELECT CategoriaId, Categoria, Descripcion, CategoriaPadreId, 1 AS nivel
      FROM dbo.Categorias WHERE CategoriaPadreId IS NULL
      UNION ALL
      SELECT C.CategoriaId, C.Categoria, C.Descripcion, C.CategoriaPadreId, P.nivel + 1
      FROM Tree AS P INNER JOIN dbo.Categorias AS C ON P.CategoriaId = C.CategoriaPadreId
     )
     SELECT CategoriaId, Categoria, Descripcion, CategoriaPadreId, nivel FROM Tree WHERE nivel <= @lbl ORDER BY Categoria

    Gracias una vez mas por toda tu ayuda!!!

    • Marked as answer by Pro2e Thursday, February 02, 2012 4:13 PM
    Thursday, February 02, 2012 4:13 PM
  • Por lo que dice aqui

    ---

    Pro2e dice,

    Necesito una forma de obtener las categorias pero hasta un nivel de jerarquia de un entero que le pase... Me explico "aunque no se si esta bien dicho, se los dire como yo lo veo"

    y

    Necesito una consulta que me retorne como maximo de jerarquia de N niveles... Es decir si le paso un valor numero "3" en este caso me deberia pasar hasta "2005 y 2008" ya que Las versiones standar y profeesional estan en un nivel 4

    ---

    Con WHERE (Transact-SQL), y Subconsultas puedes resolver el problema, ejemplo

    --use [TableName]
    --SELECT DISTINCT Categoria from dbo.Categorias
    --WHERE CategoriaPadreId= 3
    
    USE [TableName];
    GO
    SELECT DISTINCT Categoria 
    FROM dbo.Categorias 
    WHERE CategoriaPadreId IN
        (SELECT CategoriaPadreId
        FROM [AnotherTableName]
        WHERE ID = ID) ;
    GO

     


    Angel R. Jimenez G.
    Software Development
    Santo Domingo
    Republica Dominicana
    My Blog

    Hola y gracias por la ayuda, aunque creo que no me explique bien porque era otra cosa... Pero igual mil gracias....
    Thursday, February 02, 2012 4:14 PM
  • En mi ejemplo use un padre en especifico "[CategopriaId] = 1", porque es mas simple para explicar el problema. Eso no quiere decir que lo debas hacer asi, pues muchas veces lo que se sugiere es la idea, y queda de parte del OP elaborarla o desarrollarla.

    La solucion que porteastes, recorre la jerarquia completa, y luego al final escoge las filas hasta cierto nivel. Inmaginate una jerarquia con 20 niveles. Tu crees que es necesario bajar hasta el nivel 20, para luego solo filtrar las filas hasta el nivel 3, por ejemplo?

    Claro que no, y por eso te indique como parar la parte recursiva para que esta no siga mas alla del nivel que tu estas interesado.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.Categorias
    (
    CategoriaId int identity(1, 1) primary key clustered,
    Categoria varchar(20),
    CategoriaPadreId int NULL foreign key references dbo.Categorias (CategoriaId)
    );
    
    INSERT INTO Categorias (Categoria, CategoriaPadreId)
    VALUES
    	('Software', null),
    	('Hardware', null),
    	('Visual Studio', 1),
    	('2005', 3),
    	('2008', 3),
    	('Version standar', 5),
    	('Version professional', 5);
    GO
    DECLARE @lvl int = 2;
    -- query de Hunchback
    WITH Tree AS (
    SELECT
    	CategoriaId,
        Categoria,
        CategoriaPadreId,
    	1 AS lvl
    FROM
    	Categorias
    WHERE
    	CategoriaPadreId IS NULL
    
    UNION ALL
    
    SELECT
    	C.CategoriaId,
    	C.Categoria,
    	C.CategoriaPadreId,
    	P.lvl + 1
    FROM
    	Tree AS P
    	INNER JOIN
    	dbo.Categorias AS C
    	ON P.CategoriaId = C.CategoriaPadreId
    WHERE
    	P.lvl + 1 <= @lvl
    )
    SELECT
    	CategoriaId, Categoria, CategoriaPadreId, lvl
    FROM
    	Tree
    ORDER BY
    	Categoria;
    -- query de Pro2e
    WITH Tree AS (
      SELECT CategoriaId, Categoria, CategoriaPadreId, 1 AS lvl
      FROM dbo.Categorias WHERE CategoriaPadreId IS NULL
      UNION ALL
      SELECT C.CategoriaId, C.Categoria, C.CategoriaPadreId, P.lvl + 1
      FROM Tree AS P INNER JOIN dbo.Categorias AS C ON P.CategoriaId = C.CategoriaPadreId
    )
    SELECT CategoriaId, Categoria, CategoriaPadreId, lvl 
    FROM Tree 
    WHERE lvl <= @lvl 
    ORDER BY Categoria 
    GO
    DROP TABLE Categorias;
    GO
    


    Chequea ambos planes de ejecucion, para que veas como, en el query que sugeri, el filtro se hace en la parte recursiva, antes de concatenar todos los resultados. en cambio, en tu query final el filtro se hace despues que todos los resultados han sido concatenados (todos lo niveles).

    A simple vista esa diferencia no se notara en una jerarquia de a pena un par de niveles, pero el desempenio de tu query disminuira gradualmente con el nuero de niveles.

     


    AMB

    Some guidelines for posting questions...

    • Edited by HunchbackMVP Thursday, February 02, 2012 4:48 PM
    Thursday, February 02, 2012 4:46 PM
  • En mi ejemplo use un padre en especifico "[CategopriaId] = 1", porque es mas simple para explicar el problema. Eso no quiere decir que lo debas hacer asi, pues muchas veces lo que se sugiere es la idea, y queda de parte del OP elaborarla o desarrollarla.

    La solucion que porteastes, recorre la jerarquia completa, y luego al final escoge las filas hasta cierto nivel. Inmaginate una jerarquia con 20 niveles. Tu crees que es necesario bajar hasta el nivel 20, para luego solo filtrar las filas hasta el nivel 3, por ejemplo?

    Claro que no, y por eso te indique como parar la parte recursiva para que esta no siga mas alla del nivel que tu estas interesado.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.Categorias
    (
    CategoriaId int identity(1, 1) primary key clustered,
    Categoria varchar(20),
    CategoriaPadreId int NULL foreign key references dbo.Categorias (CategoriaId)
    );
    
    INSERT INTO Categorias (Categoria, CategoriaPadreId)
    VALUES
    	('Software', null),
    	('Hardware', null),
    	('Visual Studio', 1),
    	('2005', 3),
    	('2008', 3),
    	('Version standar', 5),
    	('Version professional', 5);
    GO
    DECLARE @lvl int = 2;
    -- query de Hunchback
    WITH Tree AS (
    SELECT
    	CategoriaId,
        Categoria,
        CategoriaPadreId,
    	1 AS lvl
    FROM
    	Categorias
    WHERE
    	CategoriaPadreId IS NULL
    
    UNION ALL
    
    SELECT
    	C.CategoriaId,
    	C.Categoria,
    	C.CategoriaPadreId,
    	P.lvl + 1
    FROM
    	Tree AS P
    	INNER JOIN
    	dbo.Categorias AS C
    	ON P.CategoriaId = C.CategoriaPadreId
    WHERE
    	P.lvl + 1 <= @lvl
    )
    SELECT
    	CategoriaId, Categoria, CategoriaPadreId, lvl
    FROM
    	Tree
    ORDER BY
    	Categoria;
    -- query de Pro2e
    WITH Tree AS (
      SELECT CategoriaId, Categoria, CategoriaPadreId, 1 AS lvl
      FROM dbo.Categorias WHERE CategoriaPadreId IS NULL
      UNION ALL
      SELECT C.CategoriaId, C.Categoria, C.CategoriaPadreId, P.lvl + 1
      FROM Tree AS P INNER JOIN dbo.Categorias AS C ON P.CategoriaId = C.CategoriaPadreId
    )
    SELECT CategoriaId, Categoria, CategoriaPadreId, lvl 
    FROM Tree 
    WHERE lvl <= @lvl 
    ORDER BY Categoria 
    GO
    DROP TABLE Categorias;
    GO
    


    Chequea ambos planes de ejecucion, para que veas como, en el query que sugeri, el filtro se hace en la parte recursiva, antes de concatenar todos los resultados. en cambio, en tu query final el filtro se hace despues que todos los resultados han sido concatenados (todos lo niveles).

    A simple vista esa diferencia no se notara en una jerarquia de a pena un par de niveles, pero el desempenio de tu query disminuira gradualmente con el nuero de niveles.

     


    AMB

    Some guidelines for posting questions...


    Hola perfecto entiendo ya... Anteriormente no me salia y estaba confudido por que habias puesto esto "CONVERT(varchar(900), '/' + CONVERT(varchar(10), CategoriaId) + '/') AS mat_path" pero ahora si salio bien... Gracias por tu gran ayuda de nuevo....
    Thursday, February 02, 2012 5:17 PM