Concatenating Rows of String Values for Aggregation
What would an aggregation of rows of string columns look like? For numeric columns, we can calculate averages and sums over many rows to aggregate them. Rows with string columns require a different treatment, which we'll explore in this recipe.
This recipe is useful in many-to-many relationships, when you want to aggregate one side of a relationship for reporting purposes:
- security management – a table of user and their corresponding roles in an organization
- account management – a table of sales people / account managers and the customers they manage
- inventory management – a table of stores and a list of products they hold or have sold
- tag management – a table of tags applied to resources and you want to show a summary of tags against a resource
If you have a table of store locations and the products they hold along with their quantities:
|New York||Sports Collectibles||200|
|San Francisco||Textbook Rentals||600|
Or, if your interested in the account management example, the table will look like this:
|Account Manager||Customer||Contract Value|
Usually, best practices for data management will instruct you to normalize your data into three tables – a a
store_location table, a
product table and a
product_quantity table. You can accomplish the first level of aggregation seen in the tables above with a couple of joins between them.
What we want our result to look like
|New York||Electronics, Sports Collectibles|
|San Francisco||Shoes, Textbook Rentals|
We want to create a new aggregate column
products_held on the
product column from the original table that contains string values. Furthermore, we want to sort the aggregation by the
quantity which would make the data more readily usable.
string_agg to the rescue
SELECT location, string_agg(product, ', ' ORDER BY quantity DESC) FROM products GROUP BY location;
PostgreSQL's aggregation functions are well-fleshed out. You can use other functions in the same class like
json_agg to accomplish similar results with array and json data types.
You are reading a series of Advanced SQL tricks.
Join 300+ data analysts who are using our weekly SQL tips to improve their skills. No spam, promise!