PostgreSQL 9.6 crosstab, create pivot

Question

How in PostgreSQL 9.6, from a table "import" with structure below in the link...

Structure of 'import' table

create a query / function, then transpose it to something like this:

Structure of expected view

Unfortunately, table 'import' has no ID field. I tried using crosstab with tablefunc, but no effect.

Please do not use images. Please use text which could be copied. — S-Man, Sep 09 '18 at 00:36

score 2 · Accepted Answer · answered Sep 08 '18 at 10:50

You are looking for the opposite of a cross-tab or pivot: you are looking for an un-pivot

In standard SQL you would do this with a UNION ALL (unless the DBMS supports the unpivot operator which Postgres does not):

select dlimportdate, 1 as colno, col1 as value
from the_table
union all
select dlimportdate, 2, col1
from the_table
union all
...

However in Postgres there is a shorter way to do this. Create an array of the columns, then use unnest to turn them into rows:

select dlimportdate, t.colno, t.value
from the_table
  cross join unnest(array[col1, col2, col3, ...]) with ordinality as t(value, colno);

1 Answers1