Nota de Migração: Este artigo foi migrado para o Portal Wiki em Português. O conteúdo será atualizado aqui http://social.technet.microsoft.com/wiki/pt-br/contents/articles/12572.menu-recursivo-usando-cte.aspx.



Segue abaixo um código utilizado para recursividade de menu.

Criando o ambiente
Create table Menu
(
  Codigo int identity(1,1) primary key,
  Descricao varchar(50) not null,
  CodigoPai int null
)
 
INSERT INTO Menu(Descricao) values('Arquivo'),('Editar'),('Ajuda')
INSERT INTO Menu(Descricao,CodigoPai) values('Salvar',1), ('Salvar Como',1),('Sair',1)
INSERT INTO Menu(Descricao,CodigoPai) values('Copiar',2), ('Colar',2),('Recortar',2)
INSERT INTO Menu(Descricao,CodigoPai) values('Sobre',3)
INSERT INTO Menu(Descricao,CodigoPai) values('PDF',5), ('Word',5),('Excel',5)
INSERT INTO Menu(Descricao,CodigoPai) values('Doc',12), ('Docx',12)

Execultando o comando
WITH cteMenu AS (
  --Ancora
  SELECT Codigo, Descricao, CodigoPai, Codigo AS Ordem, 1 AS Nivel
  FROM Menu
  WHERE CodigoPai IS NULL
 UNION ALL
  -- Consulta Recursiva
  SELECT M.Codigo, M.Descricao, cte.Codigo, CASE WHEN M.Codigo < 10 THEN (Ordem * 10) + M.Codigo ELSE (Ordem*100) + M.Codigo END, Nivel +1
FROM cteMenu AS cte INNER JOIN Menu AS M ON cte.Codigo = M.CodigoPai
)
SELECT Replicate('   ', Nivel - 1) + Descricao MenuCompleto, Nivel FROM cteMenu
ORDER BY Cast(Ordem AS varchar)



Para uma leitura mais completa sobre Hieraquias recomendo o artigo do Gustavo Maia.
http://www.plugmasters.com.br/sys/materias/586/1/Modelagem-de-Dados%3A-Hierarquias---Parte-1


Ozimar Henrique
http://ozimar.com