postgresql crosstab simple example

Question

I got a key-value based table where each key-value pair is assigned to an entity which is identified by an id:

|_id__|_key_______|_value_|
| 123 | FIRSTNAME | John  |
| 123 | LASTNAME  | Doe   |

And I want to transform it a structre like this:

|_id__|_firstName_|_lastName_|
| 123 | John      | Doe      |

I suppose one can use postgres build in crosstab function to do it.

Can you show me how to do it and explain why it works?

Also see this https://stackoverflow.com/q/3002499/808723 example which is slighly different. — GameScripting, Mar 01 '18 at 15:14

GameScripting · Accepted Answer · 2021-01-26T17:16:48.587

First of all activate the build in tablefunc-extension:

CREATE EXTENSION tablefunc;

Then create table and add sample data:

CREATE TABLE example (
  id int,
  key text,
  value text
);

INSERT INTO example VALUES
  (123, 'firstName', 'John'),
  (123, 'lastName', 'Doe');

Now lets prepare the crosstab statment:

SELECT *
FROM example
ORDER BY id ASC, key ASC;

Its important to have the ORDER BY here.

Result:

|_id__|_key_______|_value_|
| 123 | FIRSTNAME | John  |
| 123 | LASTNAME  | Doe   |

Solution

Now crosstab creates the table as we want:

SELECT *
FROM crosstab(
    'SELECT *
     FROM example
     ORDER BY id ASC, key ASC;'
) AS ct(id INT, firstname TEXT, lastname TEXT);

Result:

|_id__|_firstName_|_lastName_|
| 123 | John      | Doe      |

How it works #1

To however understand how it works I found it easiest to just change the ORDER BY and see what happens:

SELECT *
FROM crosstab(
    'SELECT *
     FROM example
     ORDER BY id ASC, key DESC;'
) AS ct(id INT, firstname TEXT, lastname TEXT);

Result:

|_id__|_firstName_|_lastName_|
| 123 | Doe       | John     |

As we changed the sorting of the key, the crosstab function sees the keys sorted in the other direction, thus reversing the generated columns.

How it works #2

Another thing that helped me understand how it works: the column definition is all about positions:

SELECT *
FROM crosstab(
    'SELECT *
     FROM example
     ORDER BY id ASC, key ASC;'
) AS ct(blablafirst INT, blablasecond TEXT, blablathird TEXT);

Result

|_blablafirst__|_blablasecond_|_blablathird_|
| 123          | John         | Doe         |

Also see this explanation that I found helpful: https://stackoverflow.com/a/11751905/808723 — GameScripting, Mar 01 '18 at 15:12
Doesn't the last query(`How it works #2`) should resulting in `John` under the `_blablasecond_` because of you used `key ASC`? — yshahak, Sep 24 '19 at 07:45
What if you don't know ahead of time what the "keys" are? Is there a way to make the resulting column list dynamic(ally sized)? — cloudsurfin, Sep 01 '23 at 17:09

postgresql crosstab simple example

1 Answers1

Solution

How it works #1

How it works #2

Linked