Pivoter
(sujet préalablement traité avec la version 9.4)
PIVOT...ou pas
Oracle et SQL Server proposent le mot clé PIVOT, une fonctionnalité hors standard SQL consistant à modifier la présentation des résultats. Les données des différentes lignes sont regroupées et affichées sous la forme de colonnes.
Exemple avec Oracle 12.2.0.1, d’aprés le site de Tim Hall qui avait alors utilisé une version 11.1 :
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CREATE TABLE pivot_test ( id SMALLINT, customer_id SMALLINT, product_code VARCHAR(5), quantity SMALLINT );
Table created.
INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
1 row created.
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
1 row created.
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
1 row created.
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
1 row created.
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
1 row created.
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
1 row created.
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
1 row created.
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
1 row created.
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
1 row created.
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);
1 row created.
INSERT INTO pivot_test VALUES (11, 4, 'C', 20);
1 row created.
commit;
Commit complete.
SELECT *
FROM (SELECT customer_id, product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c))
ORDER BY customer_id;
CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
1 10 20 30
2 40 50
3 60 70 80
4 100 20
4 rows selected.
Markus Winand propose une solution standard SQL:2003 en remplacement de pivot sur son site. Cette solution tire parti de FILTER que je présente notamment aussi dans un article sur la performance.
Démonstration avec PostgreSQL 9.6 :
select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
CREATE TABLE pivot_test ( id SMALLINT, customer_id SMALLINT, product_code VARCHAR(5), quantity SMALLINT );
CREATE TABLE
INSERT INTO pivot_test
VALUES
(1, 1, 'A', 10),
(2, 1, 'B', 20),
(3, 1, 'C', 30),
(4, 2, 'A', 40),
(5, 2, 'C', 50),
(6, 3, 'A', 60),
(7, 3, 'B', 70),
(8, 3, 'C', 80),
(9, 3, 'D', 90),
(10, 4, 'A', 100),
(11, 4, 'C', 20);
INSERT 0 11
SELECT
customer_id,
sum(quantity) filter (where product_code = 'A') as A_SUM_QUANTITY,
sum(quantity) filter (where product_code = 'B') as B_SUM_QUANTITY,
sum(quantity) filter (where product_code = 'C') as C_SUM_QUANTITY
FROM PIVOT_TEST
group by customer_id
order by customer_id;
customer_id | a_sum_quantity | b_sum_quantity | c_sum_quantity
-------------+----------------+----------------+----------------
1 | 10 | 20 | 30
2 | 40 | | 50
3 | 60 | 70 | 80
4 | 100 | | 20
(4 lignes)
Hélas, Oracle Database ne veut rien savoir de FILTER, même dans sa version 12.2.0.1 sortie en 2016 :
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SELECT
customer_id,
sum(quantity) filter (where product_code = 'A') as A_SUM_QUANTITY,
sum(quantity) filter (where product_code = 'B') as B_SUM_QUANTITY,
sum(quantity) filter (where product_code = 'C') as C_SUM_QUANTITY
FROM PIVOT_TEST
group by customer_id
order by customer_id;
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
Nous allons nous rabattre sur CASE, qui est implémenté bien plus largement que FILTER par les différents SGBDR.
Démonstration avec PostgreSQL 9.6 et Oracle 12.2.0.1 :
select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
SELECT
customer_id,
sum(case when product_code = 'A' then quantity end) as A_SUM_QUANTITY,
sum(case when product_code = 'B' then quantity end) as B_SUM_QUANTITY,
sum(case when product_code = 'C' then quantity end) as C_SUM_QUANTITY
FROM PIVOT_TEST
group by customer_id
order by customer_id;
customer_id | a_sum_quantity | b_sum_quantity | c_sum_quantity
-------------+----------------+----------------+----------------
1 | 10 | 20 | 30
2 | 40 | | 50
3 | 60 | 70 | 80
4 | 100 | | 20
(4 lignes)
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SELECT
customer_id,
sum(case when product_code = 'A' then quantity end) as A_SUM_QUANTITY,
sum(case when product_code = 'B' then quantity end) as B_SUM_QUANTITY,
sum(case when product_code = 'C' then quantity end) as C_SUM_QUANTITY
FROM PIVOT_TEST
group by customer_id
order by customer_id;
CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
1 10 20 30
2 40 50
3 60 70 80
4 100 20
PostgreSQL propose aussi une fonctionnalité propriétaire pour pivoter les données, la fonction CROSSTAB. Vous devez installer l’extension TABLEFUNC dans votre base aprés avoir déployé le paquet CONTRIB au niveau du système d’exploitation. Cela se fait en quelques secondes. Exemple avec root sous Debian 9 :
apt-get install postgresql-contrib
Ensuite, pour bénéficier de la fonction CROSSTAB vous devez créer l’extension dans chaque base depuis laquelle vous voulez utiliser la fonctionnalité. Exemple avec PostgreSQL 9.6 :
select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 ligne)
create extension tablefunc;
CREATE EXTENSION
La syntaxe de CROSSTAB est simple, vous fournissez la requête pour laquelle le jeu de résultats ordonné doit être pivoté :
SELECT *
FROM crosstab(
'select customer_id, product_code, sum(quantity)
from pivot_test where product_code in (''A'',
''B'',''C'') group by 1,2 order by 1,2', $$VALUES ('A'::text), ('B'::text), ('C'::text)$$)
AS pivot_test(customer_id bigint, quantity_a bigint, quantity_b bigint, quantity_c bigint);
customer_id | quantity_a | quantity_b | quantity_c
-------------+------------+------------+------------
1 | 10 | 20 | 30
2 | 40 | | 50
3 | 60 | 70 | 80
4 | 100 | | 20
(4 lignes)
À noter la partie $$VALUES ('A'::text), ('B'::text), ('C'::text)$$ qui est nécessaire pour ne pas avoir de décalage si tous les (customer_id, product_code) possibles ne sont pas présents dans pivot_test.
CureFFI.org propose une méthode pour générer dynamiquement une requête avec toutes les colonnes. Cela donne pour l’exemple précédent :
create or replace function pivotcode (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns varchar language plpgsql as $$
declare
dynsql1 varchar;
dynsql2 varchar;
columnlist varchar;
begin
-- 1. retrieve list of column names.
dynsql1 = 'select string_agg(distinct ''_''||'||colc||'||'' '||celldatatype||''','','' order by ''_''||'||colc||'||'' '||celldatatype||''') from '||tablename||';';
execute dynsql1 into columnlist;
-- 2. set up the crosstab query
dynsql2 = 'select * from crosstab (
''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2'',
''select distinct '||colc||' from '||tablename||' order by 1''
)
as newtable (
'||rowc||' varchar,'||columnlist||'
);';
return dynsql2;
end
$$;
CREATE FUNCTION
select pivotcode('pivot_test','customer_id','product_code','sum(quantity)','bigint');
pivotcode
---------------------------------------------------------------------------------------------
select * from crosstab ( +
'select customer_id,product_code,sum(quantity) from pivot_test group by 1,2 order by 1,2',+
'select distinct product_code from pivot_test order by 1' +
) +
as newtable ( +
customer_id varchar,_A bigint,_B bigint,_C bigint,_D bigint +
);
(1 ligne)
select * from crosstab (
'select customer_id,product_code,sum(quantity) from pivot_test group by 1,2 order by 1,2',
'select distinct product_code from pivot_test order by 1'
)
as newtable (
customer_id varchar,_A bigint,_B bigint,_C bigint,_D bigint
);
customer_id | _a | _b | _c | _d
-------------+-----+----+----+----
1 | 10 | 20 | 30 |
2 | 40 | | 50 |
3 | 60 | 70 | 80 | 90
4 | 100 | | 20 |
(4 lignes)
Nous pouvons noter que les quantités de "D" sont à présent affichées pour chaque customer_id dans une colonne dédiée.
Il est bien évidemment recommandé de s’appuyer sur une fonctionnalité standard SQL, plutôt que sur PIVOT avec Oracle ou CROSSTAB avec PostgreSQL.
Mise à jour : 01/06/2017