Avec PostgreSQL 9.4.6 cela peut donner en éliminant ou non les valeurs nulles :
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 TABLE unpivot_test (
id SMALLINT,
customer_id SMALLINT,
product_code_a SMALLINT,
product_code_b SMALLINT,
product_code_c SMALLINT,
product_code_d SMALLINT
);
INSERT INTO unpivot_test VALUES (1, 101, 10, 20, 30, NULL);
INSERT INTO unpivot_test VALUES (2, 102, 40, NULL, 50, NULL);
INSERT INTO unpivot_test VALUES (3, 103, 60, 70, 80, 90);
INSERT INTO unpivot_test VALUES (4, 104, 100, NULL, NULL, NULL);
SELECT id,
customer_id,
unnest(array['A', 'B', 'C', 'D' ]) AS product_code,
unnest(array [ product_code_a, product_code_b, product_code_c, product_code_d ]) AS quantity
FROM unpivot_test
ORDER BY id, customer_id;
id | customer_id | product_code | quantity
----+-------------+--------------+----------
1 | 101 | A | 10
1 | 101 | B | 20
1 | 101 | C | 30
1 | 101 | D |
2 | 102 | A | 40
2 | 102 | B |
2 | 102 | C | 50
2 | 102 | D |
3 | 103 | A | 60
3 | 103 | B | 70
3 | 103 | C | 80
3 | 103 | D | 90
4 | 104 | A | 100
4 | 104 | B |
4 | 104 | C |
4 | 104 | D |
(16 lignes)
SELECT * from
(SELECT id,
customer_id,
unnest(array['A', 'B', 'C', 'D' ]) AS product_code,
unnest(array [ product_code_a, product_code_b, product_code_c, product_code_d ]) AS quantity
FROM unpivot_test
ORDER BY id, customer_id) w
where quantity is not null;
id | customer_id | product_code | quantity
----+-------------+--------------+----------
1 | 101 | A | 10
1 | 101 | B | 20
1 | 101 | C | 30
2 | 102 | A | 40
2 | 102 | C | 50
3 | 103 | A | 60
3 | 103 | B | 70
3 | 103 | C | 80
3 | 103 | D | 90
4 | 104 | A | 100
(10 lignes)
create extension hstore;
CREATE EXTENSION
CREATE VIEW unpivot_test_v
AS
SELECT id, customer_id, (h).key, (h).value as val
FROM (SELECT id, customer_id, each(hstore(unpivot_test) - 'id'::text - 'customer_id'::text) as H
FROM unpivot_test) as W ;
CREATE VIEW
select * from unpivot_test_v;
id | customer_id | key | val
----+-------------+----------------+-----
1 | 101 | product_code_a | 10
1 | 101 | product_code_b | 20
1 | 101 | product_code_c | 30
1 | 101 | product_code_d |
2 | 102 | product_code_a | 40
2 | 102 | product_code_b |
2 | 102 | product_code_c | 50
2 | 102 | product_code_d |
3 | 103 | product_code_a | 60
3 | 103 | product_code_b | 70
3 | 103 | product_code_c | 80
3 | 103 | product_code_d | 90
4 | 104 | product_code_a | 100
4 | 104 | product_code_b |
4 | 104 | product_code_c |
4 | 104 | product_code_d |
(16 lignes)
select * from unpivot_test_v where val is not null;
id | customer_id | key | val
----+-------------+----------------+-----
1 | 101 | product_code_a | 10
1 | 101 | product_code_b | 20
1 | 101 | product_code_c | 30
2 | 102 | product_code_a | 40
2 | 102 | product_code_c | 50
3 | 103 | product_code_a | 60
3 | 103 | product_code_b | 70
3 | 103 | product_code_c | 80
3 | 103 | product_code_d | 90
4 | 104 | product_code_a | 100
(10 lignes)
alter table unpivot_test add column product_code_e smallint;
ALTER TABLE
INSERT INTO unpivot_test VALUES (5, 104, 100, NULL, NULL, NULL, 10);
INSERT 0 1
select * from unpivot_test_v where val is not null;
id | customer_id | key | val
----+-------------+----------------+-----
1 | 101 | product_code_a | 10
1 | 101 | product_code_b | 20
1 | 101 | product_code_c | 30
2 | 102 | product_code_a | 40
2 | 102 | product_code_c | 50
3 | 103 | product_code_a | 60
3 | 103 | product_code_b | 70
3 | 103 | product_code_c | 80
3 | 103 | product_code_d | 90
4 | 104 | product_code_a | 100
5 | 104 | product_code_a | 100
5 | 104 | product_code_e | 10
(12 lignes)
Même si cette fonctionnalité d’UNPIVOT n’est pas vraiment du ressort du SGBD elle est donc aussi disponible directement avec PostgreSQL, comme elle l’est avec Oracle et SQL Server.