SQL intermediate

How to Concatenate Values in SQL?

String aggregation in SQL allows you to combine multiple row values into a single string. This guide will show you how to group and concatenate effectively.

Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.

Generate this formula Browse library

The formula

Prompt

"Concatenate all values in a column into a comma-separated string, grouped by another column"

SQL

SELECT column1, STRING_AGG(column2, ', ') AS concatenated_values FROM table_name GROUP BY column1;

This SQL query concatenates all values from 'column2' into a single string, separated by commas, for each unique value in 'column1'.

Step-by-step breakdown

SELECT specifies the columns to retrieve.
STRING_AGG(column2, ', ') concatenates values from 'column2' with a comma separator.
AS concatenated_values gives a name to the resulting concatenated string.
FROM table_name specifies the table from which to retrieve the data.
GROUP BY column1 groups the results by unique values in 'column1'.

Edge cases & warnings

If 'column2' contains NULL values, they will be ignored in the concatenation.
If 'column1' has no matching rows, it will not appear in the result set.
Large datasets may hit performance issues if not indexed properly.
Different database systems may have variations in the STRING_AGG function syntax.

Examples

Prompt

"SELECT department, STRING_AGG(employee_name, ', ') FROM employees GROUP BY department;"

SQL

Sales: John, Jane; Marketing: Alice, Bob

Prompt

"SELECT category, STRING_AGG(product_name, ', ') FROM products GROUP BY category;"

SQL

Electronics: TV, Radio; Furniture: Chair, Table

Frequently asked questions

What is STRING_AGG in SQL?

STRING_AGG is a function that concatenates values from multiple rows into a single string.

Can I use STRING_AGG with ORDER BY?

Yes, you can use STRING_AGG with an ORDER BY clause to specify the order of concatenated values.

Is STRING_AGG supported in all SQL databases?

No, STRING_AGG is primarily supported in PostgreSQL and SQL Server; other databases may have different functions.

Can't find what you need?

Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.

Generate custom formula See examples