Artigos Banco de Dados Artigo SQL Magazine 46 - Construindo relatórios de referência cruzada no Microsoft SQL SERVER 2005 – Parte 2

Esse artigo faz parte da revista SQL Magazine edição 46. Clique aqui para ler todos os artigos desta edição

SPAN>

SQL Server
Construindo relatórios de referência cruzada no Microsoft SQL SERVER 2005 – Parte 2
Estudo dos operadores PIVOT e ROLLUP

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.
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.

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.
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”.

As declarações SQL mostradas neste artigo estão disponíveis para download no portal da SQL Magazine.
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.

-- Definição da CTE
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)
)

-- esta consulta faz referência à CTE definida acima
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.

SELECT CampoPrincipal, [Coluna1] AS C1, [Coluna2] AS C2, [Coluna3] AS C3
FROM (
 SELECT CampoPrincipal, CampoPivoteado, Indicador
 FROM TabelaOrigem
) AS T
PIVOT (
 FuncaoSumarizacao (Indicador)
 FOR CampoPivoteado IN ( [Coluna1], [Coluna2], [Coluna3])
...

Quer ler esse conteúdo completo? Tenha acesso completo