What is the STRING_AGG function in SQL?

The STRING_AGG function in SQL is a powerful tool that simplifies the process of combining multiple strings from different rows into a single string. This function is particularly useful when we want to display related data from multiple rows together, such as names, addresses, or any list of items stored in our database.

Understanding the STRING_AGG function

At its core, STRING_AGG takes at least two arguments. The first is the string expression we wish to combine. This would probably be a column containing string data for an SQL context. The second argument is a delimiter that will be placed between each of the combined values. This delimiter can be a comma, space, or any character you choose to neatly separate the values in the final string. Optionally, we can add an ORDER BY clause within the function to specify the order of the values.

Syntax

The basic syntax of STRING_AGG is as follows:

STRING_AGG ( expression, separator [order_by_clause] )

Example

Let’s create a simple example to illustrate how STRING_AGG works. Suppose, we have a table named Foods with one column Name that stores the names of several foods.

Step 1: Creating the table

First, we create the table using the syntax below:

CREATE TABLE Foods (
Name VARCHAR(50)
);
Create an empty table using SQL

Step 2: Inserting data

Next, we insert some names into the Foods table as follows:

INSERT INTO Foods (Name) VALUES ('Pizza');
INSERT INTO Foods (Name) VALUES ('Ramen');
INSERT INTO Foods (Name) VALUES ('Shawarma');
INSERT INTO Foods (Name) VALUES ('Kimchi');
Populate the table using SQL

Step 3: Using STRING_AGG

Now, we use the STRING_AGG function to combine these names into a single string separated by commas as follows:

SELECT STRING_AGG(Name, ', ') AS FoodsILike FROM Foods;
Aggregate strings using SQL

This command will produce a single string: "Pizza, Ramen, Shawarma, Kimchi". By changing the delimiter, we can format the string in various ways, such as using a semicolon or adding spaces.

Run the code widget below to see the output. Play around with the code by changing the delimiter.

CREATE TABLE Foods (
Name VARCHAR(50)
);
INSERT INTO Foods (Name) VALUES ('Pizza');
INSERT INTO Foods (Name) VALUES ('Ramen');
INSERT INTO Foods (Name) VALUES ('Shawarma');
INSERT INTO Foods (Name) VALUES ('Kimchi');
SELECT STRING_AGG(Name, ', ') AS FoodsILike FROM Foods;

Here’s how we can add an ORDER BY clause in the STRING_AGG function.

SELECT STRING_AGG(Name, ', ' ORDER BY Name) AS FoodsILikeInAlphateicOrder FROM Foods;

Practical uses

The STRING_AGG function can be extremely useful in reporting and data analysis. For instance, if we’re generating a report that needs to list all products purchased by a customer in a single row, we can use the STRING_AGG function to turn multiple rows of product names into a concise, comma-separated list in the report.

Moreover, STRING_AGG helps in simplifying complex data relationships. When data is spread across multiple rows that belong to a related entity (like a person or an event), using this function can help present that data in a more readable and compact form.

Conclusion

The STRING_AGG function in SQL is a straightforward yet powerful tool for concatenating row data into a single string. Its simplicity allows for easy data manipulation and presentation, making it a go-to function for many developers and analysts who work with relational databases. Whether for managing lists of names, products, or any other data type that benefits from a concise representation, STRING_AGG can help streamline data handling tasks.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved