Pivotando Dados no SQL – Parte 02

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Veja neste artigo como transportar e agregar e transporta as colunas.

 

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.


sql-25-07-2008pic04.JPG 

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.


sql-25-07-2008pic05.JPG 

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.


sql-25-07-2008pic06.JPG 

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.


sql-25-07-2008pic07.JPG 

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.


sql-25-07-2008pic08.JPG
Listagem 3. Instrução que transforma três colunas de fabricantes em duas.


sql-25-07-2008pic09.JPG 

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.


sql-25-07-2008pic10.JPG 

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.


sql-25-07-2008pic11.JPG 

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.


sql-25-07-2008pic12.JPG 

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.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?