En muchas oportunidades tenemos la necesidad de resolver este tipo de querys, por ejemplo para una tabla de empleados, de menús , una estructura de productos, etc..... Armar este tipo de querys jerárquicas no suelen ser algo simple para la mayoría de los desarrolladores. En este ejemplo que les muestro en el post hare una query jerárquica en SQL 2005 o 2008 utilizando unas nuevas sentencias TSQL llamada CTE (Common Table Expresion).

-- CREAMOS UNA TABLA EMPLEADOS

IF OBJECT_ID('DBO.EMPLEADOS') IS NOT NULL
   DROP TABLE DBO.EMPLEADOS
GO

CREATE TABLE DBO.EMPLEADOS (ID INT NOT NULL,JEFE_ID INT NULL,
                            NOMBRE VARCHAR(300),Puesto varchar(300))
GO

INSERT INTO DBO.EMPLEADOS VALUES (100,NULL,'Martin','CEO')
INSERT INTO DBO.EMPLEADOS VALUES (101,100,'Maxi','Gerente Sistemas')
INSERT INTO DBO.EMPLEADOS VALUES (102,100,'Veronica','Gerente Calidad')
INSERT INTO DBO.EMPLEADOS VALUES (103,101,'Javier','Analista SR')
INSERT INTO DBO.EMPLEADOS VALUES (104,101,'Gabriel','DBA')
INSERT INTO DBO.EMPLEADOS VALUES (105,102,'Ana','Auditora')
INSERT INTO DBO.EMPLEADOS VALUES (106,103,'Luis','Programador')
INSERT INTO DBO.EMPLEADOS VALUES (107,105,'Jose','Asistente')
GO

-- MOSTRAMOS EL ORGANIGRAMA HASTA EL NIVEL 2

WITH REPORTE(JEFE_ID, ID,NOMBRE,PUESTO,NIVEL) AS 
(
    SELECT JEFE_ID, ID,NOMBRE,PUESTO, 0 AS NIVEL
    FROM DBO.EMPLEADOS
    WHERE JEFE_ID IS NULL -- TRAEMOS TODOS LOS QUE NO TIENEN JEFES 
    UNION ALL
    SELECT e.JEFE_ID, e.ID, E.NOMBRE,E.PUESTO,NIVEL + 1
    FROM DBO.EMPLEADOS e
        INNER JOIN REPORTE d
        ON e.JEFE_ID = d.ID 
)
SELECT REPORTE.JEFE_ID, REPORTE.ID, REPORTE.NOMBRE,REPORTE.PUESTO,
EMPLEADOS.NOMBRE AS JEFE_NOMBRE, EMPLEADOS.PUESTO AS PUESTO_JEFE 
FROM REPORTE LEFT JOIN DBO.EMPLEADOS ON
REPORTE.JEFE_ID = EMPLEADOS.ID
WHERE NIVEL <= 2 
GO

En SQL Server 2008 disponemos además de esto la posibilidad de utilizar un tipo de dato jerárquico llamado Hierarchyid