How do you transpose a table in a generalizable way in SQL?

Question

How would you transpose this table:

old_table

+----+--------+-------------+-------------------+
| id | type   | field       | value             |
+----+--------+-------------+-------------------+
| 1  | person | gender      | female            |
+----+--------+-------------+-------------------+
| 1  | person | age         | 22                |
+----+--------+-------------+-------------------+
| 1  | person | name        | Julie Smith       |
+----+--------+-------------+-------------------+
| 1  | person | picture_url | www.pic.com/1.jpg |
+----+--------+-------------+-------------------+
| 2  | person | gender      | male              |
+----+--------+-------------+-------------------+
| 2  | person | name        | Johnny Parsons    |
+----+--------+-------------+-------------------+

into a table like this:

new_table

+----+----------------+--------+------+-------------------+
| id | name           | gender | age  | picture_url       |
+----+----------------+--------+------+-------------------+
| 1  | Julie Smith    | female | 22   | www.pic.com/1.jpg |
+----+----------------+--------+------+-------------------+
| 2  | Johnny Parsons | male   | Null | Null              |
+----+----------------+--------+------+-------------------+

We could do a query like this:

INSERT INTO new_table SELECT
id::id as id,
max(case when field='name' then value else null end) as name,
max(case when field='gender' then value else null end) as gender,
max(case when field='age' then value else null end) as age,
max(case when field='picture_url' then value else null end) as picture_url
FROM old_table
WHERE 1=1
AND type = 'person'
GROUP BY
id
;

Unfortunately, this query is not generalizable and if I were to add new fields, then those fields would not be added to the new table. Is there a way to write a more generalizable query?

Adding fields or tables dynamically will take dynamically generated SQL. You will need a stored procedure (or some other scripting language) to handle the addition of the new fields and the insertion into them. — JNevill
This task is sometimes called "pivoting". It's a notorious pain in the neck in MySQL. Some other table servers offer query syntax to support pivoting. — O. Jones

Marcin Zukowski Marcin Zukowski · Accepted Answer · 2016-12-10T06:03:36

PIVOT (CROSSTAB in PostgreSQL) might be what you want to use. You are effectively simulating PIVOT in your query. However, for that you need to know all the attributes/columns you have.

If you don't know up-front these values, it's possible to construct such SQL programmatically (in a client, or some databases allow executing a generated string)

How do you transpose a table in a generalizable way in SQL?

1 Answers