SQL string aggregate functions combine values from multiple rows into one text value, usually once per group. Use a function such as STRING_AGG, GROUP_CONCAT, or LISTAGG to turn related rows into a delimited list; the function name and some syntax depend on your database.
What a string aggregate does
A normal aggregate function reduces several rows to one result, as COUNT returns a number or SUM returns a total. A string aggregate does the same kind of reduction but joins text values, placing a chosen separator between them. It is useful for reports, summaries, exports, and display fields where a group of related records needs to appear as one string.
For example, an order-items table might contain several rows for each order. Grouping by order ID and aggregating product names can produce one row per order with a list such as Notebook, Pen, Folder. This is a presentation of the underlying rows, not a replacement for storing each product as a separate record.
The key ideas are:
- Input expression: the column or expression whose values are combined.
- Separator: text inserted between values, such as a comma and a space.
- Group: the set of rows reduced to one result. Without a grouping clause, the query usually aggregates all qualifying rows together.
- Ordering: an optional rule for the sequence of values in the output.
Common names across SQL databases
There is no single function name and syntax that works identically in every SQL product. Check the database engine and version before copying a query from another system.
- PostgreSQL:
STRING_AGG(value, delimiter). An ordering clause can be placed inside the function call. - SQL Server:
STRING_AGG(expression, separator), available in SQL Server 2017 and later. Ordering is specified withWITHIN GROUP (ORDER BY ...). - MySQL:
GROUP_CONCAT(...), with options for distinct values, ordering, and a separator. - SQLite:
group_concat(X, Y)is the familiar form;string_agg(X, Y)is an alias in current SQLite. Aggregate-level ordering syntax is available starting with SQLite 3.44.0. - Oracle:
LISTAGG, typically paired withWITHIN GROUP (ORDER BY ...).
These functions share a purpose, but details such as where ordering appears, how duplicates are handled, and what happens when the result is too long differ. Treat each example as dialect-specific, not as interchangeable SQL.
Basic examples by database
Suppose order_items has order_id and item_name columns. In PostgreSQL, this query makes one alphabetical list for each order:
SELECT order_id, STRING_AGG(item_name, ', ' ORDER BY item_name) AS items FROM order_items GROUP BY order_id;
In SQL Server, the equivalent ordering clause has a different position:
SELECT order_id, STRING_AGG(item_name, ', ') WITHIN GROUP (ORDER BY item_name) AS items FROM order_items GROUP BY order_id;
In MySQL, GROUP_CONCAT puts ordering and the separator inside its argument syntax:
SELECT order_id, GROUP_CONCAT(item_name ORDER BY item_name SEPARATOR ', ') AS items FROM order_items GROUP BY order_id;
In Oracle, the corresponding pattern uses LISTAGG:
SELECT order_id, LISTAGG(item_name, ', ') WITHIN GROUP (ORDER BY item_name) AS items FROM order_items GROUP BY order_id;
SQLite 3.44.0 and later can use aggregate-level ordering in this form:
SELECT order_id, group_concat(item_name, ', ' ORDER BY item_name) AS items FROM order_items GROUP BY order_id;
In every example, the GROUP BY determines the number of result rows: there is one result for each order ID represented in the qualifying input. If the desired result is one list for the entire table, omit GROUP BY. A WHERE clause filters rows before aggregation, while a HAVING clause filters groups after aggregation.
Ordering, duplicates, and NULL values
Do not assume that the values arrive in table order, insertion order, or the order used by an earlier part of the query. Without an ordering rule inside the aggregate, the resulting sequence is generally unspecified and may change with the execution plan, indexes, or database version. If list order matters, specify it in the aggregate’s supported ordering syntax.
For a stable order when values can tie, add a second key. For example, sorting by product name alone may not distinguish two rows with the same name; adding a unique item ID makes their relative order predictable. You can also order by a date column to produce a chronological list, though the order key does not have to be the same expression that is displayed.
NULL inputs are generally ignored rather than rendered as the word NULL, and separators are not inserted for ignored values. Consequently, a group containing only NULL inputs commonly produces a NULL result. If you need a visible placeholder, use an explicit expression such as COALESCE(item_name, '(unnamed)'), keeping in mind that this changes NULL rows into text before aggregation. Oracle treats a zero-length character string as NULL, a notable difference when empty strings matter.
Duplicate values are retained unless the database’s syntax supports an option to remove them and you use it. MySQL supports DISTINCT inside GROUP_CONCAT. PostgreSQL supports DISTINCT in STRING_AGG, subject to rules about ordering expressions. SQLite and SQL Server have different constraints and version-specific capabilities; if duplicate removal is essential, check the exact product documentation or deduplicate in a subquery before aggregation.
Length limits and result types
A long list may exceed a database’s configured or data-type limits. This can lead to an error or, in some systems, a truncated result. MySQL limits the maximum output of GROUP_CONCAT with the session or server setting group_concat_max_len; its default is commonly 1024 bytes, though configuration can change it. SQL Server’s result type depends on the input expression’s type, so casting to a large-value character type may be necessary when the output could be long. Oracle LISTAGG has length limits and provides an ON OVERFLOW option in supported versions to report or truncate an overflow. Review the behavior for your engine rather than assuming all products handle excess length alike.
Length limits matter especially when an aggregate is used for export or downstream processing. A user-facing preview might reasonably abbreviate a list, but a data-transfer field should not silently lose values. For large groups, consider whether a delimited text value is the right output at all. Returning one row per item is often safer for applications that need to parse, filter, or join the data later.
How to choose the right approach
Choose the native aggregate for your database and decide what the output must guarantee before writing the query. The separator, order, duplicate policy, and NULL treatment are part of the result’s meaning, not just formatting details.
- Identify the database product and version, especially if the query must run in more than one environment.
- Define the grouping key. Confirm that it creates exactly the groups the report needs.
- Choose a delimiter that is readable and appropriate for the data. A comma is convenient for display but ambiguous if values can themselves contain commas.
- Specify an ordering key if readers or downstream code rely on sequence.
- Decide whether repeated values are meaningful, and whether NULLs should be omitted or replaced.
- Estimate the largest possible output and check the engine’s length and overflow behavior.
For cross-database software, keep dialect-specific queries separate or build the list in application code after retrieving ordered rows. Application-side assembly can make behavior consistent, but it requires transferring more rows and handling grouping in the application. A database aggregate is concise and often convenient for reporting; it is not automatically the best representation for every consumer.
Common mistakes and misconceptions
A frequent mistake is placing an ORDER BY only at the end of the outer query and expecting it to control the order of items inside each string. That clause sorts result rows, not necessarily the values being concatenated within each group. Use the aggregate’s own ordering syntax when supported.
Another misconception is that a delimited result is a true array. It is just text. If a value contains the separator, splitting the output later may produce incorrect pieces unless the format has a defined escaping convention. Quoting values informally is not always sufficient, because quotes and escape characters can also occur in the data. For structured interchange, use a proper serialization format or return normalized rows.
Also distinguish string aggregation from concatenating columns within one row. An expression such as first_name || ' ' || last_name in some SQL dialects, or a dialect-specific concatenation function, combines fields from the same row. STRING_AGG and its counterparts combine values from different rows within an aggregate group. They solve related but different problems.
Frequently Asked Questions
What is the SQL function to concatenate rows?
It depends on the database: PostgreSQL and SQL Server use STRING_AGG, MySQL uses GROUP_CONCAT, SQLite commonly uses group_concat, and Oracle uses LISTAGG.
How do I add a comma between aggregated values?
Pass ', ' as the separator argument or use the database’s separator option. For example, PostgreSQL uses STRING_AGG(item_name, ', '), while MySQL uses GROUP_CONCAT(item_name SEPARATOR ', ').
Does string aggregation preserve row order?
Not reliably unless you specify ordering in the aggregate using the syntax supported by your database. An outer query’s ORDER BY sorts output rows and does not necessarily order values inside each string.
How can I remove duplicates from a string aggregate?
Use the aggregate’s supported DISTINCT option, such as MySQL’s GROUP_CONCAT(DISTINCT item_name), or select distinct values in a subquery before aggregating. Check ordering restrictions for your database.
What happens to NULL values in STRING_AGG?
In common implementations, NULL input values are skipped and do not create an extra separator. If you want a placeholder, convert NULL to text with a function such as COALESCE before aggregation.
When a string aggregate is not the best fit
String aggregates are effective when a human-readable summary is the goal: a list of tags on a report, a compact set of names, or a quick export field. They are less suitable when consumers need to query individual elements, preserve complex types, or safely round-trip arbitrary text. In those cases, keep the values relational as separate rows, or use a structured representation such as a database-supported JSON aggregate. The best choice depends on whether the result is meant for display or for further data processing.
Related
- Reverse a String in C++: std::reverse and Examples
- JavaScript split() Method: Syntax, Examples, and Tips
- JavaScript .split(): How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIFS Year Equals: How to Count by YearThe direct answer is to use the COUNTIFS function with a date range and a condition that checks…
- Remove Duplicates in Excel: Sort Data the Right WayTo remove duplicates in Excel and keep the right record, sort your data first if the order determines…
- Using SUMPRODUCT in Excel: Formulas, Examples, and Common UsesIn Excel for Microsoft 365 on Windows, SUMPRODUCT multiplies corresponding values in arrays and adds the products. If…
- SQL String Functions and Operators: A Practical GuideSQL string functions transform, inspect, search, and format text; string operators combine text or compare it with patterns.…
- Horizontal Filter in Excel: How to Filter Across ColumnsTo filter data arranged across columns in Excel for Microsoft 365, use the FILTER function with a horizontal…
- Excel Power Query Table Filter: How to Apply and RefreshTo filter an Excel table with Power Query in Microsoft 365 for Windows, select a cell in the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.