PostgreSQL has a useful function called
crosstab that makes selecting
‘has many’ data associations pretty easy. In our example we have contacts
who has many match_values (0+), and we want to present the data in a
A frequent approach I see to this problem is to make multiple queries to a database and piece the data together in a client application. Well if you have seen that being done before, this is a single query that returns the exact data for that type of problem. Instead of multiple queries, we are going to make a pivot table, join that with another table, and return a set of results that otherwise might be hard to get.
Problem we are trying to solve:
name | position | industry | nps ------+----------+----------+----- Jon | Director | Media | Beth | | Banking | 8 Amy | VP | | 10
SELECT * FROM contacts; -- id | name -- ----+------ -- 1 | Jon -- 2 | Beth -- 3 | Amy SELECT * FROM match_values; -- id | contact_id | match_category_id | name -- ----+------------+-------------------+---------- -- 1 | 1 | 1 | Director -- 2 | 1 | 2 | Media -- 3 | 2 | 2 | Banking -- 4 | 2 | 3 | 8 -- 5 | 3 | 1 | VP -- 6 | 3 | 3 | 10 select * from match_categories; -- id | name -- ----+---------- -- 1 | Position -- 2 | Industry -- 3 | NPS
The first thing we need to do is
CREATE extension tablefunc;, this will let us
Now lets take a quick high level look at how the crosstab function works for our case. We will be passing in 2 string arguments, and provide a schema for the results to be returned in.
SELECT * FROM crosstab( TEXT source_sql, TEXT category_sql ) AS ct ( result_schema )
The first argument ‘source_sql’ is a string that returns 3 columns in this order
- row_name column, how the table extends vertically
- category column, how the table extends horizontally
- value column, the values populating the columns
Our source_sql will be:
SELECT contact_id, match_category_id, name FROM match_values ORDER BY 1, 2
contact_id is the row name,
match_category_id is the column names, and
is the values.
The second argument
category_sql is the ordering of the category column, this prevents all
the null values being squashed to the left. It should return a single column of
equal length to the categories from the first query of the same type and with
Our category_sql will be:
SELECT DISTINCT id FROM match_categories ORDER BY 1
The results is a list of the column names and data types, for us to select from.
Ours columns look like this:
contact_id INT, position TEXT, industry TEXT, NPS TEXT
Now putting it all together into a full query, we get this:
SELECT * FROM crosstab ( $$ SELECT contact_id, match_category_id, name FROM match_values ORDER BY 1, 2 $$, $$ SELECT DISTINCT match_category_id FROM match_values ORDER BY 1 $$ ) AS ct ( contact_id INT, position TEXT, industry TEXT, NPS TEXT ); -- | contact_id | position | industry | nps | -- |--- |--- |--- |-- | -- | 1 | Director | Media | | -- | 2 | | Banking | 8 | -- | 3 | VP | | 10 |
The result is pretty close to what we want, but instead of contact_id we really
want a column that has the contact name. What we can do is combine our retults
with a Common Table Expression using
WITH, and join that with contacts to get exactly
what we were looking for
WITH pivot_table AS ( SELECT * from crosstab ( $$ SELECT contact_id, match_category_id, name FROM match_values ORDER BY 1 $$, $$ SELECT DISTINCT match_category_id from match_values ORDER BY 1 $$ ) AS ct ( contact_id INT, position TEXT, industry TEXT, nps TEXT ) ) SELECT contacts.name, pivot_table.position, pivot_table.industry, pivot_table.nps FROM contacts LEFT JOIN pivot_table ON contacts.id = pivot_table.contact_id; -- | name | position | industry | nps | -- |--- |--- |--- |-- | -- | Jon | Director | Media | | -- | Beth | | Banking | 8 | -- | Amy | VP | | 10 |
Now we can return exactly what we were looking for in a single query! Our database can do more of the heavy lifting and we can avoid an alternative of multiple queries and piecing the data together somewhere else.
If you want to try this out yourself, here is the SQL to generate the data in your own database:
CREATE extension tablefunc; CREATE TABLE contacts( id INT PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE match_values( id INT PRIMARY KEY, contact_id INT NOT NULL, match_category_id INT NOT NULL, name TEXT NOT NULL ); CREATE TABLE match_categories( id INT PRIMARY KEY, name TEXT NOT NULL ); INSERT INTO contacts (id, name) VALUES ( 1, 'Jon'), ( 2, 'Beth'), ( 3, 'Amy'); INSERT INTO match_categories (id, name) VALUES ( 1, 'Position'), ( 2, 'Industry'), ( 3, 'NPS'); INSERT INTO match_values (id, contact_id, match_category_id, name) VALUES ( 1, 1, 1, 'Director'), ( 2, 1, 2, 'Media'), ( 3, 2, 2, 'Banking'), ( 4, 2, 3, '8'), ( 5, 3, 1, 'VP'), ( 6, 3, 3, '10');