Con varios hilos por aquí de consultas jerárquicas resueltas a base de cursores o llamadas repetidas desde la aplicación, un recordatorio de que SQL Server tiene una herramienta hecha justo para esto: las CTE recursivas.

Ejemplo clásico, un árbol de categorías donde cada una puede tener una categoría padre:

WITH ArbolCategorias AS (
    -- caso base: las categorias raiz, sin padre
    SELECT id, nombre, id_padre, 0 AS nivel
    FROM categorias
    WHERE id_padre IS NULL

    UNION ALL

    -- caso recursivo: hijos de lo que ya tenemos en el arbol
    SELECT c.id, c.nombre, c.id_padre, ac.nivel + 1
    FROM categorias c
    INNER JOIN ArbolCategorias ac ON c.id_padre = ac.id
)
SELECT * FROM ArbolCategorias
ORDER BY nivel, nombre;

La CTE se define en dos partes unidas por UNION ALL: el caso base (de dónde arranca la recursión, aquí las categorías sin padre) y el caso recursivo, que se referencia a sí mismo (ArbolCategorias dentro de su propia definición) para ir bajando un nivel cada vez. SQL Server repite el paso recursivo hasta que deja de encontrar filas nuevas.

Casos donde de verdad compensa frente a resolverlo desde la aplicación: árboles de categorías de profundidad variable, jerarquías de organigrama (quién reporta a quién, a cuántos niveles), o listas de materiales donde un componente contiene otros componentes. Cuando la profundidad es fija y conocida (por ejemplo, siempre exactamente 2 niveles), un par de JOINs normales suele ser más simple y más rápido, la CTE recursiva brilla cuando no sabes de antemano cuántos niveles vas a tener que bajar.

Un aviso: sin límite, una CTE recursiva con datos cíclicos (A es padre de B, B es padre de A por error de datos) puede entrar en bucle infinito. SQL Server tiene un límite por defecto de 100 niveles de recursión (OPTION (MAXRECURSION n) para ajustarlo), pero merece la pena tenerlo en cuenta si tus datos pueden llegar a tener ciclos.

Ruben

El caso de uso donde de verdad brillan es cualquier estructura jerárquica de profundidad variable: organigramas, árboles de categorías con subcategorías anidadas, listas de materiales (BOM) donde un componente contiene otros componentes que a su vez contienen otros. Sin CTE recursiva, resolver esto en SQL puro implica o bien limitar a un número fijo de niveles con JOINs repetidos, o bien tirar de un cursor/bucle procedural, mucho menos elegante.

Ejemplo clásico, organigrama de empleados con jefe (id_jefe apuntando al propio id_empleado):

WITH Organigrama AS (
  SELECT id_empleado, nombre, id_jefe, 0 AS nivel
  FROM Empleados
  WHERE id_jefe IS NULL -- el/la CEO, el ancla
  UNION ALL
  SELECT e.id_empleado, e.nombre, e.id_jefe, o.nivel + 1
  FROM Empleados e
  INNER JOIN Organigrama o ON e.id_jefe = o.id_empleado
)
SELECT * FROM Organigrama OPTION (MAXRECURSION 100);

La primera parte del UNION ALL es el "ancla" (el punto de partida), la segunda es la parte recursiva que se va uniendo consigo misma nivel a nivel. Un detalle importante que pilla a mucha gente: SQL Server limita por defecto la recursión a 100 niveles (para evitar bucles infinitos si el dato tiene una referencia circular por error), así que si tu jerarquía real puede ser más profunda, necesitas el OPTION (MAXRECURSION N) explícito (o 0 para quitar el límite, con cuidado).

David