Сводная таблица

CREATE TABLE sales(YEAR INT, MONTH INT, qty INT);
INSERT INTO sales VALUES(2007, 1, 1000);
INSERT INTO sales VALUES(2007, 2, 1500);
INSERT INTO sales VALUES(2007, 7, 500);
INSERT INTO sales VALUES(2007, 11, 1500);
INSERT INTO sales VALUES(2007, 12, 2000);
INSERT INTO sales VALUES(2008, 1, 1000);
 
SELECT * FROM crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) AS (
  YEAR INT,
  "Jan" INT,
  "Feb" INT,
  "Mar" INT,
  "Apr" INT,
  "May" INT,
  "Jun" INT,
  "Jul" INT,
  "Aug" INT,
  "Sep" INT,
  "Oct" INT,
  "Nov" INT,
  "Dec" INT
);
 YEAR | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | DEC
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 ROWS)

DROP TABLE IF EXISTS sales;
CREATE TABLE sales(customer text, item text, qty INT);

CREATE OR REPLACE FUNCTION mycrosstab(src_table text)
RETURNS refcursor AS
$$
DECLARE
  coldecl text;
  colcur refcursor;
  colrec record;
  cur refcursor;
  src_sql text := 'select item, customer, qty from sales order by 1';
  cat_sql text := 'select distinct customer from sales';
BEGIN
  coldecl := 'as (item text';
  OPEN colcur FOR EXECUTE cat_sql;
  loop
    fetch NEXT FROM colcur INTO colrec;
    exit WHEN NOT found;
    coldecl := coldecl || ', "' || colrec.customer || '" int';
  END loop;
  coldecl := coldecl || ')';
  close colcur;
  --raise notice '%', coldecl; --debug
  
  cur := 'pivot' || src_table;
  OPEN cur FOR EXECUTE 'SELECT * FROM crosstab('''||src_sql||''','''||cat_sql||''')'||coldecl;
  RETURN cur;
END;
$$
LANGUAGE plpgsql;

INSERT INTO sales VALUES('Иванов', 'хлеб', 1000);
INSERT INTO sales VALUES('Иванов', 'молоко', 2000);
INSERT INTO sales VALUES('Иванов', 'колбаса', 3000);
INSERT INTO sales VALUES('Иванов', 'мясо', 4000);
INSERT INTO sales VALUES('Иванов', 'водка', 5000);
INSERT INTO sales VALUES('Петров', 'ириска', 6000);
INSERT INTO sales VALUES('Петров', 'водка', 7000);
INSERT INTO sales VALUES('Сидоров', 'хлеб', 8000);
INSERT INTO sales VALUES('Сидоров', 'молоко', 9000);
INSERT INTO sales VALUES('Сидоров', 'колбаса', 1000);
INSERT INTO sales VALUES('Сидоров', 'яблоко', 2000);
INSERT INTO sales VALUES('Сидоров', 'мандарин', 3000);

SELECT mycrosstab('sales');
fetch ALL FROM "pivotsales";

INSERT INTO sales VALUES('Новичок', 'арбуз', 1000);
INSERT INTO sales VALUES('Новичок', 'яблоко', 2000);
INSERT INTO sales VALUES('Новичок', 'мандарин', 3000);

SELECT mycrosstab('sales');
fetch ALL FROM "pivotsales";