How does the SQL version of TEXTJOIN work?

STRING_AGG joins the product names of each group into one string, using ', ' as the separator. ORDER BY inside the function makes the order of the names predictable. Like TEXTJOIN with ignore-empty set to TRUE, STRING_AGG skips null values.

Why can SQL give a different result from TEXTJOIN?

TEXTJOIN works across cells in a range; STRING_AGG works across rows within a group.

TEXTJOIN in SQL

TEXTJOIN combines the values of several cells into one text with a separator; the SQL equivalent is STRING_AGG, which joins values from many rows.

The Excel version

=TEXTJOIN(", ", TRUE, B2:B10)

The SQL equivalent

This example assumes: order_items(id bigint, order_id bigint, product_name text, quantity numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT order_id,
       STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM order_items
GROUP BY order_id
ORDER BY order_id;

How it works

STRING_AGG joins the product names of each group into one string, using ', ' as the separator.
ORDER BY inside the function makes the order of the names predictable.
Like TEXTJOIN with ignore-empty set to TRUE, STRING_AGG skips null values.

Where the results differ from Excel

TEXTJOIN works across cells in a range; STRING_AGG works across rows within a group.
Joining columns within one row uses CONCAT or the || operator rather than STRING_AGG.
STRING_AGG is PostgreSQL and SQL Server; MySQL uses GROUP_CONCAT and BigQuery uses STRING_AGG too.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

List the products in each order as a single comma-separated column.
Show each customer with all their order dates in one cell.