La clause PIVOT permet d’afficher les lignes d’une requête sous forme de colonnes à la manière des tableaux croisés dynamique d’Excel. Le mieux, pour comprendre, c’est de partir d’un exemple.
Voici une requête qui ramène par département et par « job », la somme des salaires de la table SCOTT.EMP :
select deptno, job, sum(sal) sal
from emp
group by deptno, job;
DEPTNO JOB SAL
---------- --------- ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000
Mettons que vous vouliez avoir un tableau avec, une ligne pour chaque job et une colonne pour chaque numéro de département ; la somme des salaires doit être l’intersection de chaque ligne et colonne… Le tableau que vous voulez ressemble à ceci :
JOB DEPT10 DEPT20 DEPT30
--------- ---------- ---------- ----------
CLERK 1300 1900 950
SALESMAN 5600
PRESIDENT 5000
MANAGER 2450 2975 2850
ANALYST 6000
En 10g, on peut s’en sortir avec les fonctions DECODE ou CASE ! En 11g, la clause PIVOT rend la requête facile à écrire :
WITH qry AS (select deptno, job, sum(sal) sal
from emp
group by deptno, job)
select * from qry
pivot (sum(sal) for deptno in
(10 as dept10, 20 as dept20, 30 as dept30));
Vous remarquerez qu’avec l’affichage en SQL, on est obligé de préciser toutes les valeurs des départements (le texte en bleu). On aimerait bien pouvoir dire quelque chose comme ceci :
select * from qry
pivot (sum(sal) for deptno in
(select deptno from dept));
Malheureusement, ce n’est pas (encore ?) possible. Toutefois, si vous pouvez afficher ou manipuler le résultat en XML avec une feuille de style ou un langage de programmation, la syntaxe devient possible et vous pouvez ne pas supposer le nombre de lignes ou de colonnes :
set long 300
with qry as (select deptno, job, sum(sal) sal
from emp
group by deptno, job)
select job,deptno_xml from qry
pivot xml (sum(sal) for deptno in (any));
JOB DEPTNO_XML
--------- --------------------------------------------
ANALYST <PivotSet>
<item>
<column name = "DEPTNO">20</column>
<column name = "SUM(SAL)">6000</column>
</item>
</PivotSet>
CLERK <PivotSet>
<item>
<column name = "DEPTNO">10</column>
<column name = "SUM(SAL)">1300</column>
</item>
<item>
<column name = "DEPTNO">20</column>
<column name = "SUM(SAL)">1900</column>
</item>
<item>
<column name = "DEPTNO">30</column>
<column name = "SUM(SAL)">950</column>
</item>
</PivotSet>
[...]
Si vous voulez, vous pouvez aussi générer tout le contenu dans un seul fichier XML avec les opérateurs XML de la base de données :
set long 3000
select sys_xmlagg(myrow) as "RESULT" from (
with qry as (select deptno, job, sum(sal) sal
from emp
group by deptno, job)
select xmlelement("ROW",
xmlforest(job,deptno_xml)) as "MYROW" from qry
pivot xml (sum(sal) for deptno in (any)));
RESULT
-----------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<JOB>ANALYST</JOB>
<DEPTNO_XML>
<PivotSet>
<item>
<column name="DEPTNO">20</column>
<column name="SUM(SAL)">6000</column>
</item>
</PivotSet>
</DEPTNO_XML>
</ROW>
<ROW>
<JOB>CLERK</JOB>
<DEPTNO_XML>
<PivotSet>
<item>
<column name="DEPTNO">10</column>
<column name="SUM(SAL)">1300</column>
</item>
<item>
<column name="DEPTNO">20</column>
<column name="SUM(SAL)">1900</column>
</item>
<item>
<column name="DEPTNO">30</column>
<column name="SUM(SAL)">950</column>
</item>
</PivotSet>
</DEPTNO_XML>
</ROW>
<ROW>
<JOB>MANAGER</JOB>
<DEPTNO_XML>
<PivotSet>
<item>
<column name="DEPTNO">10</column>
<column name="SUM(SAL)">2450</column>
</item>
<item>
<column name="DEPTNO">20</column>
<column name="SUM(SAL)">2975</column>
</item>
<item>
<column name="DEPTNO">30</column>
<column name="SUM(SAL)">2850</column>
</item>
</PivotSet>
</DEPTNO_XML>
</ROW>
<ROW>
<JOB>PRESIDENT</JOB>
<DEPTNO_XML>
<PivotSet>
<item>
<column name="DEPTNO">10</column>
<column name="SUM(SAL)">5000</column>
</item>
</PivotSet>
</DEPTNO_XML>
</ROW>
<ROW>
<JOB>SALESMAN</JOB>
<DEPTNO_XML>
<PivotSet>
<item>
<column name="DEPTNO">30</column>
<column name="SUM(SAL)">5600</column>
</item>
</PivotSet>
</DEPTNO_XML>
</ROW>
</ROWSET>
le contraire, à savoir transformer un tableau en ligne, est également possible ! La clause UNPIVOT permet par exemple de regrouper les colonnes SAL et COMM de la table SCOTT.EMP et de spécifier une valeur dans la colonne « TYPE » pour les différencier :
col EMPNO format 9999
select empno,
ename,
type,
sal_comm
from emp
unpivot (sal_comm
for type in (SAL as 'Salary',
COMM as 'Commission'));
EMPN ENAME TYPE SAL_COMM
---- ---------- ---------- ----------
7369 SMITH Salary 800
7499 ALLEN Salary 1600
7499 ALLEN Commission 300
7521 WARD Salary 1250
7521 WARD Commission 500
7566 JONES Salary 2975
7654 MARTIN Salary 1250
7654 MARTIN Commission 1400
7698 BLAKE Salary 2850
7782 CLARK Salary 2450
7788 SCOTT Salary 3000
7839 KING Salary 5000
7844 TURNER Salary 1500
7844 TURNER Commission 0
7876 ADAMS Salary 1100
7900 JAMES Salary 950
7902 FORD Salary 3000
7934 MILLER Salary 1300
Comme vous pouvez le voir dans l’exemple ci-dessus, les lignes de la table d’origine ont
été dupliquées pour contenir les différentes valeurs des colonnes sur lesquelles le UNPIVOT est appliqué. Les colonnes ayant des valeurs NULL ne génèrent pas, quant à elles, de lignes supplementaires.
2 réflexions sur “PIVOT et UNPIVOT”
Tu as raison ! Tu peux référencer les alias de la clause PIVOT dans la requête :
select job, sum(DEPT10), sum(DEPT20), sum(DEPT30)
from emp pivot (sum(sal) for deptno in
(10 as dept10, 20 as dept20, 30 as dept30))
group by job;
Ça ne m’avait même pas effleuré l’esprit ! Merci !!!
tu peux obtenir ton tableau sans sous-requête, non?
select job,sum(« 10 »),sum(« 20 »),sum(« 30 ») from emp pivot (sum(sal) for deptno in (10,20,30)) group by job;
Les commentaires sont fermés.