CriarWeb.com > Manuais > Tutorial de SQL

Uma consulta de referências cruzadas é aquela que nos permite visualizar os dados em filas e em colunas, estilo tabela, por exemplo:

Produto / Ano	1996	1997
Calças	1.250	3.000
Camisas	8.560	1.253
Sapatos	4.369	2.563

Se tivermos uma tabela de produtos e outra tabela de pedidos, podemos visualizar em total de produtos pedidos por ano para um artigo determinado, tal e como se visualiza na tabela anterior. A sintaxe para este tipo de consulta é a seguinte:

TRANSFORM função agregada instrução select PIVOT campo pivot
[IN (valor1[, valor2[, ...]])]

Onde:

função agregada	É uma função SQL agregada que opera sobre os dados selecionados.
instrução select	É uma instrução SELECT.
campo pivot	É o campo ou expressão que deseja utilizar para criar os cabeçalhos da coluna no resultado da consulta.
valor1, valor2 São valores fixos utilizados para criar os cabeçalhos da coluna.

Para resumir dados utilizando uma consulta de referência cruzada, se selecionam os valores dos campos ou expressões especificadas como cabeçalhos de colunas de tal forma que podem se ver os dados em um formato mais compacto que com uma consulta de seleção.

TRANSFORM é opcional, porém se for incluída é a primeira instrução de uma cadeia SQL. Precede à instrução SELECT que especifica os campos utilizados como cabeçalhos de fila e uma cláusula GROUP BY que especifica o agrupamento das filas. Opcionalmente pode incluir outras cláusulas como, por exemplo, WHERE, que especifica uma seleção adicional ou um critério de ordenação.

Os valores devolvidos em campo pivot se utilizam como cabeçalhos de coluna no resultado da consulta. Por exemplo, ao utilizar as cifras de vendas no mês da venda como pivot em uma consulta de referência cruzada se criariam 12 colunas. Pode restringir o campo pivot para criar cabeçalhos a partir dos valores fixos (valor1, valor2) listados na cláusula opcional IN.

Também pode incluir valores fixos, para os que não existem dados, para criar colunas adicionais.

Exemplos

TRANSFORM
   Sum(Quantidade) AS Vendas
SELECT
   Produto, Quantidade
FROM
   Pedidos
WHERE
   Data Between #01-01-1998# And #12-31-1998#
GROUP BY
   Produto
ORDER BY
   Produto
PIVOT
   DatePart("m", Data)
(Cria uma consulta de tabela de referências cruzadas que mostra as vendas de produtos por mês para um ano específico. Os meses aparecem da esquerda à direita como colunas e os nomes dos produtos aparecem de cima para baixo como filas.)

TRANSFORM
   Sum(Quantidade) AS Vendas
SELECT
    Companhia
FROM
    Pedidos
WHERE
    Data Between #01-01-1998# And #12-31-1998#
GROUP BY
    Companhia
ORDER BY
   Companhia
PIVOT
    "Trimestre " &
    DatePart("q", Data)
    In ('Trimestre1', 'Trimestre2', 'Trimestre 3', 'Trimestre 4')
(Cria uma consulta de tabela de referências cruzadas que mostra as vendas de produtos por trimestre de cada provedor no ano indicado. Os trimestres aparecem da esquerda à direta como colunas e os nomes dos provedores aparecem de cima para baixo como filas.)

Um caso prático:

Trata-se de resolver o seguinte problema: temos uma tabela de produtos com dois campos, o código e o nome do produto. Temos outra tabela de pedidos na que anotamos o código do produto, a data do pedido e a quantidade pedida. Desejamos consultar os totais de produto por ano, calculando a média anual de vendas.

Estrutura e dados das tabelas:


Para resolver a consulta sondaremos a seguinte consulta:

TRANSFORM
   Sum(Pedidos.Quantidade) AS Resultado
SELECT
   Nombre AS Produto, Pedidos.Id AS Código,
    Sum(Pedidos.Quantidade) AS TOTAL,
    Avg(Pedidos.Quantidade) AS Media
FROM
    Pedidos, Artigos
WHERE
   Pedidos.Id = Artigos.Id
GROUP BY
    Pedidos.Id, Artigos.Nome
PIVOT
    Year(Data)

E obtemos o seguinte resultado:

Produto	Código	Total	Media	1996	1997
Sapatos	1	348	87	300	48
Calças	2	955	238,75	375	580
Blusas	3	1940	485	620	1320

Comentários à consulta:

A cláusula TRANSFORM indica o valor que desejamos visualizar nas colunas que realmente pertencem à consulta, neste caso 1996 e 1997, visto que ademais colunas são opcionais. SELECT especifica o nome das colunas opcionais que desejamos visualizar, neste caso, Produto, Código, Total e Média, indicando o nome do campo que desejamos mostrar em cada coluna ou o valor da mesma. Se incluirmos uma função de cálculo o resultado se fará baseando-se nos dados da fila atual e não ao total dos dados.

FROM especifica a origem dos dados. A primeira tabela que deve figurar é aquela de onde desejamos extrair os dados, esta tabela deve conter ao menos três campos, um para os títulos da fila, outros para os títulos da coluna e outro para calcular o valor das células.

Neste caso em concreto se desejava visualizar o nome do produto, como na tabela de pedidos só figurava o código do mesmo se adicionou uma nova coluna na cláusula select chamada Produto que se corresponda com o campo Nome da tabela de artigos. Para vincular o código do artigo da tabela de pedidos com o nome do mesmo da tabela artigos, foi inserido a cláusula INNER JOIN.

A cláusula GROUP BY especifica o agrupamento dos registros, contrariamente aos manuais de instrução esta cláusula não é opcional já que deve figurar sempre e devemos agrupar os registros pelo campo do qual extrairemos a informação. Neste caso existem dois campos dos que extraímos a informação: pedidos.quantidade e artigos.nome, por isso, agrupamos pelos campos.

Para finalizar, a cláusula PIVOT indica o nome das colunas não opcionais, neste caso 1996 e 1997 e como vamos ao dado que aparecerá nas colunas, neste caso empregamos o ano em que se produziu o pedido, extraindo-o do campo pedidos.fecha.

Outras possibilidades de data da cláusula pivot são as seguintes:

1. Para agrupamento por Trimestres:
   PIVOT "Tri " & DatePart("q",[Data]);
2. Para agrupamento por meses (sem ter em conta o ano)
   PIVOT Format([Data],"mmm") In ("Jan", "Fev", "Mar", "Abr", "Maio", "Jun", "Jul", "Ago", "Set", "Out", "Nov", "Dez");
3. Para agrupar por dias
   PIVOT Format([Data],"Short Date");