SPAN>
SQL Server No artigo anterior desta série, vimos os recursos básicos da linguagem T-SQL que são normalmente usados em consultas relacionadas a Business Intelligence (BI). Na verdade, as cláusulas, operadores e funções ali expostos devem fazer parte da “caixa de ferramentas” de qualquer desenvolvedor que use a linguagem SQL. Tabela 2. Relatório de referência cruzada de demanda mensal por cidade. E, é claro, se os relatórios fossem mais extensos, maior seria a vantagem da referência cruzada em relação à lista de dados. As declarações SQL mostradas neste artigo estão disponíveis para download no portal da SQL Magazine. -- Definição da CTE -- esta consulta faz referência à CTE definida acima SELECT CampoPrincipal, [Coluna1] AS C1, [Coluna2] AS C2, [Coluna3] AS C3
Construindo relatórios de referência cruzada no Microsoft SQL SERVER 2005 – Parte 2
Estudo dos operadores PIVOT e ROLLUP
Neste artigo vamos um pouco mais além. Vamos explorar, de fato, a geração de relatórios de referência cruzada (crosstab) baseados em linguagem SQL.
Se você fizer uma consulta rápida no Google antes de ler este artigo, vai ver que existe uma unanimidade entre os autores que escrevem sobre este tema: todos dizem que, se possível, você deve evitar usar linguagem SQL para tarefas como esta.
De fato, o SQL não é destinado para tarefas que envolvam tratamento da informação. Existem várias ferramentas e algumas linguagens no mercado que são destinadas a este tipo de tarefa, como a linguagem MutiDimensional Expressions (MDX), que se mostram mais simples, mais flexíveis e de melhor performance que aquela que obtemos com a linguagem SQL em tarefas desta natureza.
Dito isso, este artigo vai lhe ajudar naquelas situações em que simplesmente não há outra opção com a mesma relação de custo/benefício. A propósito, todos os recursos que apresentamos nesta série de artigos estão disponíveis em todas as versões do SQL Server 2005. Até mesmo no SQL SERVER 2005 EXPRESS, que é uma versão gratuita do software!
A seguir, vamos explorar alguns exemplos de relatórios de referência cruzada combinando operadores para preparação de informações sumarizadas.
Além disso, apresentamos aqui uma novidade do SQL SERVER 2005: o recém adotado operador PIVOT.
Relatórios de referência cruzada
Retomamos o nosso estudo sobre relatórios de referência cruzada da forma mais óbvia: exibindo a facilidade de leitura que ele nos proporciona quando o comparamos com uma lista de dados normal. Não há o que discutir. Basta observar as Tabelas 1 e 2. 
Tabela 1. Demanda mensal por cidade.
Novamente, criaremos os nossos exemplos usando a base de dados “Farmaceutico”, que registra transações de demanda de produtos farmacêuticos. O diagrama desta base é exibido na Figura 1.
Figura 1. Diagrama do banco de dados “Farmaceutico”.
Criando um relatório de referência cruzada usando CASE
Até pouco tempo atrás, o único recurso que tínhamos para criar um relatório de referência cruzada era através do uso da função CASE, que descreveremos novamente a seguir. A estratégia é separar manualmente os dados em colunas distintas para termos o efeito da referência cruzada. E, em geral, precisamos usar a função SUM e a cláusula GROUP BY em seguida.
A Listagem 1 exibe a declaração SQL que resulta em um relatório idêntico ao exibido da Tabela 2. Observe que nosso exemplo faz uso de uma CTE para tornar mais legível a sentença.
WITH TOP5CIDADE_CTE (Cidade, Mes, ValorReal)
AS (
SELECT C.Cidade, YEAR(T.Data)*100+MONTH(T.Data) AS Mes, SUM(T.ValorReal) AS ValorReal
FROM tblDemanda T
INNER JOIN tblBrick B ON T.fkBrick = B.pkBrick
INNER JOIN tblCidade C ON B.fkCidade = C.pkCidade
WHERE T.Data BETWEEN '20060101' AND '20060331' AND
B.fkCidade IN (--Aqui selecionamos as Cidades TOP 5
SELECT TOP 5 XC.pkCidade
FROM tblDemanda XT
INNER JOIN tblBrick XB ON XT.fkBrick = XB.pkBrick
INNER JOIN tblCidade XC ON XB.fkCidade = XC.pkCidade
WHERE XT.Data BETWEEN '20060101' AND '20060331'
GROUP BY XC.pkCidade
ORDER BY SUM(XT.ValorReal)DESC
)
GROUP BY C.Cidade, YEAR(T.Data)*100+MONTH(T.Data)
)
SELECT X.Cidade,
SUM(CASE WHEN X.Mes = '200601' THEN X.ValorReal ELSE 0 END) AS '200601',
SUM(CASE WHEN X.Mes = '200602' THEN X.ValorReal ELSE 0 END) AS '200602',
SUM(CASE WHEN X.Mes = '200603' THEN X.ValorReal ELSE 0 END) AS '200603'
FROM TOP5CIDADE_CTE AS X
GROUP BY X.Cidade
ORDER BY 1
Listagem 1 Relatório de referência cruzada usando a função CASE.
No exemplo da Listagem 1, temos uma função CASE para cada coluna do relatório, de modo a caracterizar os resultados de cada mês. Assim, na primeira coluna dos meses, a função CASE testa se o registro se refere ao mês de Janeiro/2006. Se o resultado for verdadeiro, a função retorna o valor da demanda. Se o resultado for falso, retorna o valor zero (0). Desta maneira, ao calcularmos a soma dos resultados daquela coluna, teremos exatamente a demanda ocorrida no mês de Janeiro/2006 naquela cidade.
O mesmo procedimento se repete nas demais colunas. Portanto, se formos construir um relatório com o resultado mensal do ano todo, usaremos doze funções CASE dentro da declaração.
Criando o mesmo relatório usando PIVOT
O SQL Server 2005 incorporou o operador PIVOT à T-SQL, cuja função é reorganizar a exibição dos dados de modo que se apresentem como um relatório de referência cruzada.
Junto com o operador PIVOT, foi introduzido também o operador UNPIVOT, que tem a função inversa: converter referências cruzadas em listas de dados. Neste artigo, vamos nos ater apenas ao primeiro deles.
A Listagem 2 exibe um modelo simplificado da sintaxe usada com o operador PIVOT.
FROM (
SELECT CampoPrincipal, CampoPivoteado, Indicador
FROM TabelaOrigem
) AS T
PIVOT (
FuncaoSumarizacao (Indicador)
FOR CampoPivoteado IN ( [Coluna1], [Coluna2], [Coluna3])
...
