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?

Answer 1

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         |