Creating Postgres table out of variable table data

Question

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#.

http://www.craigkerstiens.com/2013/06/27/Pivoting-in-Postgres/ — Robert Harvey, Apr 15 '14 at 17:52
@RobertHarvey: Lots of running scripts and blinking stuff and remote sites requesting more permission to run more scripts on that page, but not that much content. — Erwin Brandstetter, Apr 15 '14 at 18:16
@ErwinBrandstetter: It's a pivot. There is a workable technique in there amidst the pixie dust. — Robert Harvey, Apr 15 '14 at 18:20

score 1 · Answer 1 · edited May 23 '17 at 12:11

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

Creating Postgres table out of variable table data

1 Answers1