Pivoter
(sujet mis à jour avec la version 9.6)
PIVOT ou...CROSSTAB
Oracle et SQL Server proposent le mot clé PIVOT pour 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 11.1.0.x d’aprés le site de Tim Hall :
CREATE TABLE pivot_test (
id NUMBER,
customer_id NUMBER,
product_code VARCHAR2(5),
quantity NUMBER
);
INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);
INSERT INTO pivot_test VALUES (11, 4, 'C', 20);
COMMIT;
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.
Il s’agit de "cross tabulation" et PostgreSQL propose pour cela 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 8 :
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.4.6 :
select version();
version
-----------------------------------------------------------------------------------------------
PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 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é. L’exemple basique du site de Tim Hall donne avec PostgreSQL 9.4.6 :
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.
Ce site propose une méthode pour générer dynamiquement une requête avec toutes les colonnes. Cela donne pour l’exemple précédent, toujours avec PostgreSQL 9.4.6 :
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)
Même si cette fonctionnalité de PIVOT ou CROSSTAB n’est pas vraiment du ressort du SGBD elle est disponible directement avec PostgreSQL, comme elle l’est avec Oracle et SQL Server.
Mise à jour : 14/02/2016