Consultas pivô e transposição de linhas em colunas

Márcio d'Ávila, 21 de janeiro de 2007. Revisão 1, 6 de outubro de 2012.
Categoria: Banco de Dados: SQL

A sumarização e agrupamento de resultados em consultas pivô sobre bancos de dados, em geral com a transposição de linhas em colunas no resultado, são necessidades com as quais um desenvolvedor SQL pode se deparar. Esta situação é mais comum quando se trabalha em aplicações analíticas sobre dados, típicas de consultas OLAP em modelos de Inteligência de Negócios (BI - Business Intelligence) sobre Armazém de Dados (DW - Data Warehouse).

O livro de Tom Kyte, Expert One-on-One Oracle [1], traz a seguinte definição para Consulta Pivô (Pivot Query):

Uma consulta pivô é quando você toma um conjunto de dados como:

C1    C2    Cx    Cy
----- ----- ----- -----
a1    b1    x1    y1
a1    b1    x2    y2
a1    b1    x3    y3
…

e deseja exibir no seguinte formato:

C1    C2    x1    x2    x3
----- ----- ----- ----- -----
a1    b1    y1    y2    y3
…

Um exemplo

Suponha que você tenha a seguinte consulta de total de cargos por departamento:

select cargo, deptno, count(*) total
from colaborador
group by cargo, deptno
/
CARGO         DEPTNO      TOTAL
---------- ---------- ----------
ANALISTA           20          5
GERENTE            10          1
GERENTE            20          1
GERENTE            30          1
PRESIDENTE         10          1
TÉCNICO            10          3
TÉCNICO            20          2
TÉCNICO            30          8

8 linhas selecionadas.

E queira montar uma relação concisa de cargos (exibidos como linhas) por departamento (quantidades exibidas como colunas). Conhecendo o universo dos departamentos existentes, pela primeira consulta, sabemos que eles são três, tendo códigos 10, 20 e 30.

Assim, partimos da primeira consulta como fonte de dados (subconsulta ou consulta aninhada) e utilizamos a função SQL DECODE() para “filtrar” e transpor cada departamento conhecido para uma coluna resultante distinta:

select cargo
, decode(deptno, 10, total, null) dept_10
, decode(deptno, 20, total, null) dept_20
, decode(deptno, 30, total, null) dept_30
from ( select cargo, deptno, count(*) total
       from colaborador
       group by cargo, deptno )
/
CARGO        DEPT_1O    DEPT_2O    DEPT_3O
---------- ---------- ---------- ----------
ANALISTA                       5
GERENTE             1
GERENTE                        1
GERENTE                                   1
PRESIDENTE          1
TÉCNICO             3
TÉCNICO                        2
TÉCNICO                                   8

8 linhas selecionadas.

Por fim, utilizamos a função de grupo MAX() para reunir os totais de cada cargo em uma única linha:

select cargo
, max( decode(deptno, 10, total, null) ) dept_10
, max( decode(deptno, 20, total, null) ) dept_20
, max( decode(deptno, 30, total, null) ) dept_30
from ( select cargo, deptno, count(*) total
       from colaborador
       group by cargo, deptno )
group by cargo
/
CARGO        DEPT_1O    DEPT_2O    DEPT_3O
---------- ---------- ---------- ----------
ANALISTA                       5
GERENTE             1          1          1
PRESIDENTE          1
TÉCNICO             3          2          8

4 linhas selecionadas.

Cláusulas Pivô no Oracle 11g

O Oracle 11g introduziu novas cláusulas PIVOT e UNPIVOT, que simplificam muito a sintaxe SQL para montagem de consultas com pivotagem.

Veja como fica o exemplo anterior com a nova cláusula pivot:

select *
from ( select cargo, deptno
       from colaborador )
pivot
(
  count(deptno)
  for deptno in (10 as dept_10, 20 as dept_20, 30 as dept_20)
)
/

A cláusula IN é obrigatória, mas, se ao invés de enumerar quais valores literais devem compor a consulta pivô, você quiser todos, utilize ANY:

select *
from ( select cargo, deptno
       from colaborador )
pivot
(
  count(deptno)
  for deptno in (any)
)
/

Para mais informações, consulte as referências a seguir.

Referências

A seguir há uma coletânea de referências (em inglês) que explicam e exemplificam soluções para esta necessidade, com foco na sintaxe e nos recursos SQL de Oracle:

Novos operadores de pivô no Oracle 11g (em inglês):

Referências bibliográficas (em inglês):
Firefox - A web de volta
Creative Commons License

© 2003-2013, Márcio d'Ávila, mhavila.com.br, direitos reservados. O texto e código-fonte apresentados podem ser referenciados, distribuídos e utilizados, desde que expressamente citada esta fonte e o crédito do(s) autor(es). A informação aqui apresentada, apesar de todo o esforço para garantir sua precisão e correção, é oferecida "como está", sem quaisquer garantias explícitas ou implícitas decorrentes de sua utilização ou suas conseqüências diretas e indiretas.