A CTE (Common Table Expression) é um recurso do SQL Server que foi introduzido na versão 2005. A CTE pode ser referenciada N vezes em uma mesma consulta, é parecida com uma tabela derivada.
Um recurso que considero muito útil no CTE é a utilização de recursividade, irei demonstrar como utilizar no seguinte cenário:
É preciso armazenar categorias e subcategorias, sendo que as subcategorias não tem limite hierárquico, ou seja, uma categoria pode ter N filhos e assim por diante.
Primeiro irei criar uma tabela de categoria


CREATE TABLE categoria(
     id INT PRIMARY KEY IDENTITY(1, 1),
     nome VARCHAR(30) NOT NULL,
     id_categoria INT NULL
 );

Acima determino que a tabela tenha uma chave primaria chamada id e que ela deve começar do número 1 e acrescentar de 1 em 1 a cada novo INSERT; Também determino que deve ter uma coluna nome de 30 caracteres no máximo que não pode ser nula; E por ultimo criei uma coluna id_categoria que faz referencia ao id da categoria que a tupla é filha, essa coluna permite valor nulo, caso a categoria seja subcategoria.
Em seguida adiciono uma chave estrangeira para coluna id_categoria, para manter consistência entre os dados de categoria.


ALTER TABLE categoria
ADD CONSTRAINT fk_categoria FOREIGN KEY(id_categoria) REFERENCES categoria(id);

Acima adiciono a chave estrangeira.

Abaixo executo uma carga de dados, para termos dados de exemplo.


DECLARE @idCategoria INT;
INSERT INTO categoria (nome, id_categoria) VALUES('Ferramentas manuais', NULL);
SET @idCategoria = @@IDENTITY;
INSERT INTO categoria (nome, id_categoria) VALUES('Alicate', @idCategoria);
INSERT INTO categoria (nome, id_categoria) VALUES('Chave', @idCategoria);
INSERT INTO categoria (nome, id_categoria) VALUES('Chave de fenda', @idCategoria);
INSERT INTO categoria (nome, id_categoria) VALUES('Martelo', @idCategoria);
INSERT INTO categoria (nome, id_categoria) VALUES('Multimetro', @idCategoria);

INSERT INTO categoria (nome, id_categoria) VALUES('Lâmpadas', NULL);
SET @idCategoria = @@IDENTITY;
INSERT INTO categoria (nome, id_categoria) VALUES('Lâmpada anti-inseto', @idCategoria);
INSERT INTO categoria (nome, id_categoria) VALUES('Lâmpada bolinha', @idCategoria);
INSERT INTO categoria (nome, id_categoria) VALUES('Lâmpada circular', @idCategoria);
SET @idCategoria = @@IDENTITY;
INSERT INTO categoria (nome, id_categoria) VALUES('Lâmpada circ. para fogão', @idCategoria);
INSERT INTO categoria (nome, id_categoria) VALUES('Lâmpada circ. para teto', @idCategoria);

Os dados devem ficar dessa forma

grid1

Abaixo segue o primeiro exemplo do select recursivo com CTE


WITH subCategoria AS(
   SELECT id, nome
   FROM categoria
   WHERE id = 1
   UNION ALL
   SELECT sub.id, sub.nome
   FROM categoria AS sub
   INNER JOIN subCategoria ON(subCategoria.id = sub.id_categoria)
)
SELECT * FROM subCategoria

Primeiro nomeio a consulta como subcategoria, e dentro do corpo do CTE executo 2 select unidos pelo UNION ALL, o primeiro select determina o ponto de partida, nesse caso esta partindo da categoria com o id 1; No segundo select realizado quase o mesmo select, a diferença é que foi adicionado um INNER JOIN com o próprio CTE(repare com quem é feito o INNER JOIN), as colunas do INNER JOIN devem ser: o id da categoria com a coluna id_categoria; Outro detalhe é que como o select é feito em cima da mesma tabela os nome de colunas irão ser os mesmos, nesse caso é necessário dar apelidos nas tabelas, com esse INNER JOIN definimos nosso ponto de parada da recursividade. E por ultimo é preciso fechar o corpo do CTE e realizar o select do CTE com o nome que foi dado a ela: subcategoria, o select com CTE deve apresentar o seguinte resultado:

grid2

O select funcionou como deveria, só foi mostrado a categoria pai e suas subcategorias, para facilitar a visualização irei adicionar uma coluna numérica informando qual o nível que a categoria se encontra como segue.


;WITH subCategoria AS(
SELECT id, nome, 1 AS nivel
          FROM categoria
          WHERE id = 1
          UNION ALL
          SELECT sub.id, sub.nome, nivel + 1 AS nivel
          FROM categoria AS sub
          INNER JOIN subCategoria ON(subCategoria.id = sub.id_categoria)
 )
 SELECT * FROM subCategoria

Adicionei a coluna nível com valor fixo em 1 no primeiro select(ponto de partida) e adicionei a coluna nível também(lembrando que um union dele ter a mesma quantidade de colunas no selects e os tipos tem que corresponderem) no segundo select, acrescentando + 1, para subir de nível; O select deve apresentar o seguinte resultado

grid3

Caso mude o id do filtro da consulta do ponto de partida para 7 (categoria Lâmpadas) o seguinte resultado dever apresentado

grid4

Repare que o nível chegou até 3, isso por que o segundo nível de subcategoria tem subcategorias também.

E para finalizar, modifiquei o select para melhorar a visualização; adicionando a função REPLICATE, para criar uma visualização hierarquica


WITH subCategoria AS(
SELECT id, nome, 1 AS nivel
        FROM categoria
        WHERE id = 7
        UNION ALL
        SELECT sub.id, sub.nome, nivel + 1 AS nivel
        FROM categoria AS sub
        INNER JOIN subCategoria ON(subCategoria.id = sub.id_categoria)
 )
 SELECT REPLICATE(' ', nivel - 1) + nome, nivel FROM subCategoria

Resultado:

grid5

Espero que tenham gostado, até uma próxima.