# PostgreSQL STRING_AGG() Function With Examples

> The STRING_AGG() function in Postgres is used to concatenate multiple strings into a single string, separated by a specific delimiter/separator.

PostgreSQL offers various built-in string manipulation functions. One such function is **STRING_AGG()** which comes up with the ease and efficiency of aggregating multiple strings into a single. It is one of the aggregate functions that Postgres supports and is widely used to concatenate the list of strings.

This blog will explain what exactly the **STRING_AGG()** is and how to use it in Postgres. For this purpose, the below-listed concepts will be covered in this post:

\- How to Use STRING_AGG() Function in Postgres?  
\- Generating a Comma-separated List of Values Using STRING_AGG() Function.  
\- Generating a Comma-Separated List of Values From Multiple Columns.

 **How to Use STRING_AGG() Function in Postgres?**

The STRING_AGG() function in Postgres is used to concatenate multiple strings into a single string, separated by a specific delimiter/separator. The syntax for using STRING_AGG() is:
    
    
    STRING_AGG(col_name/expression, delimiter[order_by_clause]);

Here, in the above syntax,

\- The “col_name/expression” represents the column whose values you want to concatenate.  
\- The delimiter represents the separator between each value, such as “ **,** ”, “ **-** ”, etc.  
\- The “order_by_clause” specifies the order in which the values should be aggregated.

 **Generating a Comma-separated List of Values Using STRING_AGG() Function**

To generate a comma-separated list of values, you need to pass an expression and a comma as arguments to the STRING_AGG() function.

 **Example: How to Use STRING_AGG() Function on a Single Column?**

We have created a sample table named “staff_info” that contains the following data:

Let’s use the **STRING_AGG()** function to generate a list of comma-separated values for the “ **staff_info** ” table:
    
    
    SELECT staff_designation, STRING_AGG(staff_name, ',')
    FROM staff_info 
    GROUP BY staff_designation;

The above example uses the **STRING_AGG()** function to get a comma-separated list of employee names. The GROUP BY clause is used to group the data with respect to designation:

The output proves that the STRING_AGG() function retrieves the comma-separated list of employees’ names.

 **Generating a Comma-Separated List of Values From Multiple Columns**

The **STRING_AGG()** function accepts only two arguments, an expression, and a separator. So if you want to aggregate multiple columns using STRING_AGG(), then you must concatenate the column names in the first argument and specify the separator in the second argument. Use the concatenation symbol “||” to concatenate various columns. Alternatively, multiple **STRING_AGG()** functions can be used in a single query to generate a comma-separated list of values from various columns.

 **Example 1: STRING_AGG() Function on Multiple Columns Using Concatenation Operator**

We have created two sample tables named “employee_info” and “department_info”, whose details are shown in the following snippets:

The department_info table contains the following data:

Now we will learn how to use the STRING_AGG() function to multiple columns:
    
    
    SELECT dpt_name,
    STRING_AGG (e_id ||' ' || e_name, ', ') AS emp_details
    FROM employee_info
    INNER JOIN department_info USING (e_id)
    GROUP BY dpt_name;

In the above code:

\- Multiple columns are passed as the first argument to the **STRING_AGG()** function.  
\- The concatenation symbol combines multiple columns, and the comma “,” is used as a separator/delimiter.  
\- The **INNER JOIN** joins the employee_info and department_info tables.  
\- The **GROUP BY** clause groups the table’s data with respect to the department name.

This is how you can use the STRING_AGG() function to generate a list of comma-separated values from different columns.

 **Example 2: Using Multiple STRING_AGG() Functions on Multiple Columns**

In the following example, we will show you how to use multiple STRING_AGG() functions on multiple tables to generate a list of comma-separated values from different columns:
    
    
    SELECT dpt_name,
    STRING_AGG (e_name, ', '), STRING_AGG (e_email, ', ')
    FROM employee_info
    INNER JOIN department_info USING (e_id)
    GROUP BY dpt_name;

The output authenticates the working of the STRING_AGG() function.

 **Conclusion**

PostgreSQL offers a **STRING_AGG()** function, which comes up with the ease and efficiency of aggregating multiple strings into one. It accepts two arguments: an expression(list of strings) and a separator; consequently, it concatenates the given set of strings into a single string, separated by a specified delimiter/separator. This post explained the usage of the STRING_AGG() function with suitable examples.

---
[View this page online](https://www.commandprompt.com/education/postgresql-string_agg-function-with-examples/)