table2matrix

Transform table like queries in denormalized table

language: also available in:

Case of use

You need to denormalize a table into a matrix like table.

CREATE TABLE sales (years integer, items text, subsidiary text, total decimal);
INSERT INTO sales values
  (2018,'audio', 'Madrid', 39800),
  (2018,'audio', 'Paris', 359230),
  (2018,'media', 'Madrid', 73530),
  (2018,'media', 'Paris', 359230),
  (2017,'texts', 'Paris', 499230),
  (2018,'texts', 'Madrid', 94530);

SELECT table2matrix('sales', 'years,items', 'subsidiary', 'total', 'sales_matrix');

SELECT * FROM sales_matrix;

years	items	Madrid	Paris
2018	audio	39800	359230
2018	media	73530	359230
2017	texts		499230
2018	texts	94530

Install

psql < bin/create_table2matrix.sql

License

MIT