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
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.
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:
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:
The results is a list of the column names and data types, for us to select from.
Ours columns look like this:
Now putting it all together into a full query, we get this:
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
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: