Transpose data in postgres table

Question

I need to transpose data as shown in the tables below. I will need this to be done dynamically on any number of columns (they can have from 1 up to 100), and i also only want to transpose some of the data. Note that there can be up to millions of rows.

we have new data like this (read from a csv into temp table):

id      | name      | field1    | field2    | field3
1       | igig      | a         | b         | cde
2       | ihiuh     | gf        | hi        | pl
3       | pio       | zsfs      | oij       | ugu
4       | xrxf      | iuhi      | vxz       | pkm
5       | rwtre     | xrsd      | as        | jhb

we need to do query magic to transpose results like this:

id      | column_name   | value
1       | name          | igig
1       | field1        | a
1       | field2        | b
1       | field3        | cde
2       | name          | ihiuh
2       | field1        | gf
2       | field2        | hi
...

we want to join this new result set with another table.

So you are only asking for the transposing step? In other cases we would need a better sample and expected output. :) — S-Man
– S-Man, Commented Aug 22, 2019 at 15:32

S-Man · Accepted Answer · 2019-08-22 15:26:27Z

1

The transposing can be done using a JSON operation:

step-by-step demo:db<>fiddle

SELECT 
    id,
    elements ->> 'column_name' AS column_name,
    elements ->> 'value' AS value
FROM (
    SELECT
        id,
        json_build_object('column_name', 'name', 'value', name) AS name,
        json_build_object('column_name', 'field1', 'value', field1) AS field1,
        json_build_object('column_name', 'field2', 'value', field2) AS field2,
        json_build_object('column_name', 'field3', 'value', field3) AS field3
    FROM
        mytable
)s,
unnest(ARRAY[name, field1, field2, field3]) AS elements

Putting the values and column names into one JSON object
Aggregate these columns into one array
These array elements can be unnested which results in on row per array element, but all in the same column
This column contains all generated JSON objects. So, finally, you are able to parse the original column names and values from this JSON column and give them out in two separate columns.

Magic!

answered Aug 22, 2019 at 15:26

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brandon Henry Over a year ago

we did something similar but using unnest instead. i'll post an answer. this probably works, but ours was simpler.

Brandon Henry · Accepted Answer · 2019-10-10 22:56:30Z

1

We went with:

SELECT id,
       unnest(ARRAY['name','field1','field2']) AS field_name,
       unnest(ARRAY["name", "field1", "field2"]) AS new_value
       FROM table

edited Oct 10, 2019 at 22:56

answered Sep 27, 2019 at 16:03

Brandon Henry

3,7492 gold badges27 silver badges32 bronze badges

Collectives™ on Stack Overflow

Transpose data in postgres table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related