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 …
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.
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.
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):
© 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.