Pivotal or Crosstab Tables

something about cross tab

CREATE EXTENSION IF NOT EXISTS tablefunc;

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 ct;


SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text);


SELECT * FROM crosstab (
    '
        SELECT location, year, SUM(raindays)::int
        FROM rainfalls
        GROUP BY 
            location,
            year
        ORDER BY
            location,
            year
    '
) AS ct 
(
    "LOCATION" TEXT,
    "2012" INT,
    "2013" INT,
    "2014" INT,
    "2015" INT,
    "2016" INT,
    "2017" INT
)