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 C3 ----- ----- ------ a1 b1 x1 a1 b1 x2 a1 b1 x3 …e deseja exibir no seguinte formato:
C1 C2 C3(1) C3(2) C3(3) ----- ----- ----- ----- ----- a1 b1 x1 x2 x3 …
Suponha que você tenha a seguinte consulta de total de cargos por departamento:
sql> select cargo, deptno, count(*) total 2 from colaborador 3 group by cargo, deptno 4 /
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:
sql> select cargo 2 , decode(deptno, 10, total, null) dept_10 3 , decode(deptno, 20, total, null) dept_20 4 , decode(deptno, 30, total, null) dept_30 5 from ( select cargo, deptno, count(*) total 6 from colaborador 7 group by cargo, deptno ) 8 /
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:
sql> select cargo 2 , max( decode(deptno, 10, total, null) ) dept_10 3 , max( decode(deptno, 20, total, null) ) dept_20 4 , max( decode(deptno, 30, total, null) ) dept_30 5 from ( select cargo, deptno, count(*) total 6 from colaborador 7 group by cargo, deptno ) 8 /
CARGO DEPT_1O DEPT_2O DEPT_3O ---------- ---------- ---------- ---------- ANALISTA 5 GERENTE 1 1 1 PRESIDENTE 1 TÉCNICO 3 2 8 4 linhas selecionadas.
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:
© 2003-2007, 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.