Can my DB?

-- Concatenate product names per category
SELECT
  category,
  STRING_AGG(name, ', ' ORDER BY name) AS product_list
FROM products
GROUP BY category
ORDER BY category;

-- With DISTINCT
SELECT STRING_AGG(DISTINCT status, ', ' ORDER BY status)
FROM orders;

• ORDER BY clause inside STRING_AGG controls concatenation order.
• NULL values are automatically skipped.
• DISTINCT can be used to deduplicate values before aggregation.
• ARRAY_AGG is available for array output instead of string concatenation.

-- Concatenate product names per category
SELECT
  category,
  GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS product_list
FROM products
GROUP BY category
ORDER BY category;

-- With DISTINCT
SELECT GROUP_CONCAT(DISTINCT status ORDER BY status SEPARATOR ', ')
FROM orders;

• Default separator is comma; use SEPARATOR keyword for a custom delimiter.
• Result truncated at group_concat_max_len (default 1024 bytes); raises a warning, not an error.
• Increase with SET SESSION group_concat_max_len = 1000000 for larger results.
• DISTINCT supported for deduplication.

-- GROUP_CONCAT (MySQL-compatible)
SELECT category,
  GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS product_list
FROM products
GROUP BY category;

-- STRING_AGG (PostgreSQL/SQL Server compatible, MariaDB 10.3.3+)
SELECT category,
  STRING_AGG(name, ', ' ORDER BY name) AS product_list
FROM products
GROUP BY category;

• Supports both GROUP_CONCAT (MySQL-compatible) and STRING_AGG (added in 10.3.3).
• STRING_AGG syntax is compatible with PostgreSQL and SQL Server.
• group_concat_max_len system variable limits GROUP_CONCAT output length.

-- Basic LISTAGG
SELECT
  category,
  LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS product_list
FROM products
GROUP BY category
ORDER BY category;

-- With overflow handling (Oracle 12.2+)
SELECT LISTAGG(name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
       WITHIN GROUP (ORDER BY name)
FROM products;

• LISTAGG added in Oracle 11.2.
• WITHIN GROUP (ORDER BY ...) is required syntax.
• Result limited to 4000 characters (VARCHAR2 limit) by default.
• ON OVERFLOW TRUNCATE (12.2+) handles graceful truncation with an indicator.
• LISTAGG also works as an analytic (window) function since Oracle 12.2.

-- STRING_AGG (SQL Server 2017+)
SELECT
  category,
  STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS product_list
FROM products
GROUP BY category
ORDER BY category;

-- Pre-2017 workaround using FOR XML PATH
SELECT category,
  STUFF((
    SELECT ', ' + name
    FROM products p2
    WHERE p2.category = p.category
    ORDER BY name
    FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 2, '') AS product_list
FROM products p
GROUP BY category;

• STRING_AGG added in SQL Server 2017.
• WITHIN GROUP (ORDER BY ...) controls concatenation order.
• Pre-2017 workaround uses FOR XML PATH with STUFF() to remove the leading delimiter.
• No built-in length limit (up to NVARCHAR(MAX)).

-- GROUP_CONCAT with custom separator
SELECT
  category,
  GROUP_CONCAT(name, ', ') AS product_list
FROM products
GROUP BY category;

-- Default separator is comma
SELECT GROUP_CONCAT(name) FROM products WHERE category = 'electronics';

• GROUP_CONCAT is the only string aggregation function.
• Default separator is a comma.
• No ORDER BY clause inside the aggregate; use a subquery or CTE to pre-sort.
• No length limit enforced by SQLite itself (limited by available memory).

SELECT
  category,
  LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS product_list
FROM products
GROUP BY category
ORDER BY category;

• LISTAGG with WITHIN GROUP (ORDER BY ...) is the string aggregation function.
• Result limited to varchar maximum size.
• WITHIN GROUP ORDER BY is supported for deterministic ordering.

SELECT
  category,
  LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS product_list
FROM products
GROUP BY category
ORDER BY category;

• LISTAGG added in DB2 9.7 Fix Pack 4 on LUW.
• WITHIN GROUP (ORDER BY ...) controls ordering.
• Older versions use XMLAGG-based workarounds.
• Result length is limited by the VARCHAR size limit.

-- LISTAGG
SELECT
  category,
  LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS product_list
FROM products
GROUP BY category
ORDER BY category;

-- ARRAY_AGG for array output
SELECT category, ARRAY_AGG(name) FROM products GROUP BY category;

• LISTAGG is the string aggregation function.
• WITHIN GROUP (ORDER BY ...) controls ordering.
• ARRAY_AGG is available for producing array output instead of a string.
• LISTAGG result size limited by Snowflake's VARCHAR max (16 MB).

-- STRING_AGG with ORDER BY and LIMIT
SELECT
  category,
  STRING_AGG(name, ', ' ORDER BY name LIMIT 100) AS product_list
FROM products
GROUP BY category
ORDER BY category;

-- ARRAY_AGG for array output
SELECT category, ARRAY_AGG(name ORDER BY name) FROM products GROUP BY category;

• STRING_AGG supports ORDER BY inside the aggregate for deterministic ordering.
• LIMIT clause inside STRING_AGG caps the number of items included.
• NULL values are ignored by default.
• ARRAY_AGG is also available for array output.
• STRING_AGG(DISTINCT ...) for deduplication.