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