In this shot, we’ll look at how to concatenate multiple rows of a column together with a delimiter.
For example, consider the following table:
| Name | Age |
|---|---|
| Sam | 22 |
| John | 24 |
| Gabe | 26 |
After concatenating all the rows from the Name column, the output should look as follows:
| Concatenated Name |
|---|
| Sam, John, Gabe |
string_agg() functionThe string_agg() function allows us to concatenate a list of strings with the specified separator in between.
STRING_AGG(expression, separator [order_by_clause])
expression: This can be any valid expression that results in a character string.
separator: This is the separator to use to concatenate the strings.
order_by_clause: This is an optional clause that helps in the ordering of the concatenated results.
CREATE TABLE Person (Name varchar(100) NOT NULL,Age int);INSERT INTO Person(Name, Age)VALUES('George', 20),('Emma', 22),('Harry',15),('Ava',17),('Olivia',25),('Thomas',23);SELECT STRING_AGG(Name, ', ') as "Concatenated Name" from Person;
Lines 2–5: We create a table Person using the CREATE TABLE query.
Lines 7–15: We insert some records using the INSERT query.
Line 17: We use the STRING_AGG query to concatenate the rows of the column Name into a single string.
CREATE TABLE Person (Name varchar(100) NOT NULL,Age int);INSERT INTO Person(Name, Age)VALUES('George', 20),('Emma', 22),('Harry',15),('Ava',17),('Olivia',25),('Thomas',23);SELECT STRING_AGG(Name, ', ' ORDER BY Age DESC) as "Concatenated Name" from Person;
Lines 2–5: We create a table Person using the CREATE TABLE query.
Lines 7–15: We insert some records using the INSERT query.
Line 17: We use the STRING_AGG query to concatenate the rows of the column Name into a single string. Here, we use the ORDER BY clause to specify the order in which the rows are concatenated. As per the value (Age) that we pass, the rows are ordered by the Age column in descending order before they are concatenated into a single string.