String aggregation and unnesting are great ways used to manipulate and transform text data. We will be covering 2 functions here STRING_AGG() for string aggregation and unnesting using regexp_split_to_table().

Using STRING_AGG()

The STRING_AGG() function aggregates (or concatenate, you remember that word) multiple string values from rows into a single string, with a specified delimiter.

Hmm, it probably makes no sense right now, let's go over a simple example. Let's say you have this orders table with 2 columns: Order_ID and Product_Name.

To create a list of products for each order, we combine the product names into one row, separated by commas. For instance, if Order 1 contains 'Apple' and 'Banana', we'll show it as "Apple, Banana" in a single row for Order 1.

And we will be able to do it using the STRING_AGG() function!

Yes, here is the SQL code:

SELECT 
    order_id, 
    STRING_AGG(product_name, ', ') AS product_list
FROM orders
GROUP BY 1;

For each product_id, we are aggregating all the product names of that order together. And seperating each product name with a comma & space as a delimiter!