Postgres unterstützt seit 9.5 auch Kreuztabellen. Zuerst muss man das contrib-Paket installieren:
apt-get install postgresql-contrib
und dann in der DB die extension aktivieren:
CREATE EXTENSION tablefunc;
Beispiel:
CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
SELECT *
FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = att2 or attribute = att3
order by 1,2')
AS mycrosstab(row_name text, category_1 text, category_2 text, category_3 text);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |
Beispiel für SuperX:
SELECT * from crosstab( 'select sem_rueck_beur_ein::varchar(255) as sem_rueck_beur_ein,geschlecht::varchar(255) as geschlecht, sum(summe)::integer as summe FROM kenn_stg_aggr group by 1,2 order by 1,2' ) AS ct(sem_rueck_beur_ein varchar(255), geschlecht_m integer,geschlecht_w integer);
ergibt:
sem_rueck_beur_ein | geschlecht_m | geschlecht_w --------------------+--------------+-------------- 20081 | 2116 | 779 20082 | 2564 | 893 20091 | 2311 | 814 20092 | 2796 | 974 (4 rows)
Hier ist ein Link auf das Thema in der Postgres Doku vom 9.5: