Search code examples
sqlpostgresqlunpivot

PostgreSQL 9.6 crosstab, create pivot

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.

Solution

  • 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);