Pivotando Dados no SQL – Parte 02
Resumo
Em muitas situações a modelagem das tabelas do sistema foi feita de tal maneira que, para enquadrar os dados nos formatos dos relatórios, é necessário transportar os dados que estão nas linhas para colunas e vice-versa. Este artigo mostra como transportar os dados através de exemplos que utilizam a instrução SELECT, para os casos onde a os dados a serem transportados já são conhecidos.
Transportando colunas
Em alguns modelos de dados as estruturas das tabelas são criadas de acordo com o modo na qual as informações são obtidas, geralmente através de um dispositivo de entrada especial, como um leitor de código de barras ou sensores específicos. O modelo de dados que armazena resultados sobre uma prova de vestibular é um bom exemplo deste tipo de modelo, pois um leitor óptico é utilizado para obter as alternativas marcadas na folha de respostas.
Neste modelo, cada prova contém várias questões de múltipla escolha onde o candidato pode escolher a alternativa A, B ou C para cada questão. As provas são dividias em tipos, para evitar que haja 'cola'. O tipo indica qual é a ordem das questões e tipos de provas válidos são AM (amarelo), AZ (Azul) e VE (Verde). Os dados de todas as provas de todos os candidatos estão armazenados na tabela chamada TB_DADOS_PROVA. Algumas linhas desta tabela são mostrados na Tabela 3.
Tabela 3. Dados sobre provas de vestibular.
Sabe-se pela estrutura da prova que somente três alternativas são possíveis, para cada questão. Baseado nisso, um relatório apontando quanto de cada alternativa, para cada questão e tipo de prova, foi solicitado. De acordo com o formato do relatório, para que os dados da tabela se enquadrem no formato é necessária a criação de três novas colunas, uma para cada alternativa, e a colocação dos dados das quantidades de cada alternativa nas linhas destas três novas colunas.
O valor das três novas três colunas, que chamaremos de QTD_A, QTD_B e QTD_C, deve se basear no valor da coluna ALTERNATIVA. Se o valor da coluna ALTERNATIVA for ‘A’, deve-se colocar o valor da coluna QTD_ALTERNATIVA na coluna QTD_A. O mesmo raciocínio deve ser aplicado para as outras duas colunas, QTD_B e QTD_C.
A implementação desta idéia deve ser feita através de uma estrutura que checa o valor de uma coluna dentro da instrução SELECT. O SQL Server possui a estrutura CASE, que é similar à função IFF de várias linguagens de programação, para a verificação do valor de uma coluna durante a execução de uma instrução SELECT.
A Listagem 1 mostra a instrução SELECT que gera o relatório com as três novas colunas e a Tabela 4 mostra o relatório solicitado. Infelizmente esta idéia não pode ser aplicada para situações onde não sabemos qual é o universo de dados que uma determinada coluna pode apresentar, pois neste caso seria necessário utilizar instruções SQL dinâmicas montadas de acordo com os dados.
Listagem 2. Instrução SELECT que cria as três novas colunas.
Para cada linha do relatório somente uma das três novas colunas apresentadas na Tabela 4 possui um valor diferente de zero. Isso é uma característica dos dados que pode ser visualizada mais facilmente pelo formato dos dados apresentados na Tabela 4.
Tabela 4. Relatório sobre provas de um vestibular.
Também podemos pivotar os dados que estão apresentados da mesma maneira que a Tabela 4 e transformá-los no formato mais simples. A Tabela 5 mostra um exemplo de vendas de modelos de celulares por três empresas: ACME, TABAJARA e CHING_LING. Os dados estão armazenados na tabela chamada TB_VENDA_CELULAR.
Tabela 5. Dados sobre vendas de modelos de celulares.
Para transportar os dados de modo que as colunas com os nomes das empresas cedam lugar para duas novas colunas, FABRICANTE e QUANTIDADE, devemos aplicar novamente a estrutura CASE. Para a coluna FABRICANTE devemos verificar se o valor da coluna ACME é maior que zero e, caso positivo, retornar a string ‘ACME’. Caso negativo, devemos verificar se a coluna TABAJARA é maior que zero aninhando as estruturas CASE.
A mesma idéia vale para a coluna QUANTIDADE, porém neste caso o valor das colunas será retornado e não uma string. A Listagem 3 traz a instrução SELECT utilizada para criar as colunas FABRICANTE e QUANTIDADE a partir dos dados da tabela TB_VENDA_CELULAR. A Tabela 6 mostra o resultado da execução desta instrução.
Listagem 3. Instrução que transforma três colunas de fabricantes em duas.
Tabela 6. Relatório sobre as vendas de aparelhos celulares.
Agregando e transportando
O último exemplo que veremos neste artigo faz uma agregação nos dados e transporta os valores de duas colunas para três linhas. Os dados são referentes à informação nutricional de alguns alimentos e foram armazenados na tabela TB_INFO_NUTRICAO, mostrada na Tabela 7.
Tabela 7. Dados nutricionais de alimentos.
O relatório solicitado agrega as informações dos alimentos de maneira que, para cada alimento, as informações sobre valor calórico, gorduras totais e colesterol estejam separas em três colunas.
Este tipo de manipulação exige agregação e transporte dos dados. A idéia aqui é utilizar a estrutura CASE, como visto anteriormente, dentro da função de agregação MAX, agregando os dados pelo valor da coluna NOME_ALIMENTO. Para cada registro agregado devemos verificar o valor da coluna NOME_INFO e, de acordo com seu conteúdo, separar o valor em três novas colunas. A Listagem 4 mostra a instrução SELECT que faz esta manipulação de dados.
Listagem 4. Instrução que e transporta informações nutricionais agregando por alimento.
O resultado da execução da instrução SELECT apresentada na Listagem 4 pode ser visto na Tabela 8.
Tabela 8. Informações nutricionais de alimentos agregadas por alimento.
Conclusão
Neste artigo foi mostrado como gerar relatórios que não são facilmente obtidos através das tabelas contidas no modelo de dados. O transporte de dados que estão em linhas para colunas foi apresentado através de exemplos práticos, onde funções de manipulação de dados foram utilizadas junto a funções de agregações.
