CODEWITHSUNDEEP
sqlpostgresqltransformtransposedatabase-table
user3537023
user3537023

Reputation: 13

Creating Postgres table out of variable table data

I have a Postgres table of data that looks like this (ex.):

╔════╦═══════════╦════════════╗
║ ID ║ FieldName ║ FieldValue ║
╠════╬═══════════╬════════════╣
║ 66 ║ PO#       ║   11111111 ║
║ 66 ║ Zip       ║      01810 ║
║ 66 ║ Badge     ║   22222222 ║
║ 67 ║ PO#       ║    7777777 ║
║ 67 ║ Zip       ║      02144 ║
║ 67 ║ Badge     ║   99999999 ║
╚════╩═══════════╩════════════╝

My question is how to transform this table into a new table that looks like this:

╔════╦══════════╦════════╦══════════╗
║ ID ║  Field1  ║ Field2 ║  Field3  ║
╠════╬══════════╬════════╬══════════╣
║ 66 ║ 11111111 ║  01810 ║ 22222222 ║
║ 66 ║  7777777 ║  02144 ║ 99999999 ║
╚════╩══════════╩════════╩══════════╝

I need to do it entirely with Postgres syntax. Ideally I would be able to also dynamically figure out how many fields there were but that is a secondary need. For now, the assumption is that there would be three fields and I need to essentially transpose them into these new columns called Field#.

Upvotes: 0

Views: 74

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657587

Use the crosstab() function from the tablefunc module. Detailed instruction and links here (read first!):
PostgreSQL Crosstab Query

Your query could look like:

SELECT *
FROM   crosstab(
         'SELECT "ID", "FieldName", "FieldValue"
          FROM   tbl
          ORDER  BY 1'

         ,$$VALUES ('PO#'::text), ('Zip'), ('Badge')$$
       ) AS ct ("Section" text, "Field1" int, "Field2" int, "Field3" int);

To dynamically figure out how many fields there were, you would have to build the above statement dynamically. SQL demands to know the return type. Much more details under this related question:
Dynamic alternative to pivot with CASE and GROUP BY

Upvotes: 1

Related Questions