LIST Function - Sybase SQL Anywhere to SQL Server Migration

Sybase SQL Anywhere LIST aggregate function concatenates strings from multiple rows into a single string.

In Microsoft SQL Server you can use STRING_AGG function, but it is available since SQL Server 2017 only , so you can also use STUFF function and FOR XML PATH clause of SELECT statement.

	Sybase SQL Anywhere	SQL Server
Syntax	LIST([ALL | DISTINCT] exp [, delimiter] [ORDER BY exp2 [ASC | DESC], … ])	STRING_AGG(exp, delimiter) [WITHIN GROUP (ORDER BY exp2 [ASC | DESC], …) ]
Delimiter	Comma ',' by default	Must be specified, no default
Distinct Values	Supported	Not supported, a subquery can be used

Consider the following sample table:

  -- Sample table
  CREATE TABLE colors (name VARCHAR(30));
 
  -- Sample data
  INSERT INTO colors VALUES ('Red');
  INSERT INTO colors VALUES ('Orange');
  INSERT INTO colors VALUES ('Blue');
  INSERT INTO colors VALUES ('Orange');

Sybase SQL Anywhere:

  -- Aggregate values, default delimiter is comma
  SELECT LIST(name) FROM colors;
  # Red,Orange,Blue,Orange
 
  -- Use ; as delimiter
  SELECT LIST(name, ';') FROM colors;
  # Red;Orange;Blue;Orange
 
  -- Order by name
  SELECT LIST(name ORDER BY name) FROM colors;
  # Blue,Orange,Orange,Red
 
  -- Unique values only
  SELECT LIST(DISTINCT name) FROM colors;
  # Blue,Orange,Red

SQL Server:

  -- Aggregate values, default must be specified
  SELECT STRING_AGG(name, ',') FROM colors;
  # Red,Orange,Blue,Orange
 
  -- Order by name
  SELECT STRING_AGG(name, ',') WITHIN GROUP (ORDER BY name) FROM colors;
  # Blue,Orange,Orange,Red
 
  -- Unique values only (using a subquery)
  SELECT STRING_AGG(name, ',') 
  FROM (SELECT DISTINCT name FROM colors) c;
  # Blue,Orange,Red

In earlier SQL Server versions, you can use FOR XML XPATH clause of the SELECT statement to concatenate strings from multiple rows.

When FOR XML clause is specified SQL Server joins all rows into a single XML row as follows:

SQL Server:

  -- Generate single row XML 
  SELECT name FROM colors FOR XML PATH('')
  # <name>Red</name><name>Orange</name><name>Blue</name><name>Orange</name>

You can see XML tag around values. To remove them you can concatenate each value with a string literal:

  -- Generate single row without XML tags 
  SELECT ', ' + name FROM colors FOR XML PATH('')
  # , Red, Orange, Blue, Orange

Now we can just remove the leading comma ',' using STUFF function:

  -- Removing the leading comma  
  SELECT STUFF((SELECT ', ' + name FROM colors FOR XML PATH('')), 1, 2, '')
  # Red, Orange, Blue, Orange

Single column in SELECT list and with GROUP BY

When there is GROUP BY clause you can use the following statement in SQL Server:

   --  Implementing LIST for single column with GROUP BY 
   SELECT STUFF((SELECT ', ' + name FROM cities WHERE state = c.state FOR XML PATH('')),1,2,'')
   FROM cities c
   GROUP BY state

Result:

Concatenate all or unique values

In the previous examples, the queries concatenate all values and the same value can be specified multiple times:

  -- Concatenate values allowing duplicates
  SELECT STUFF((SELECT ', ' + state FROM cities FOR XML PATH('')), 1, 2, '')

Result:

But you can use DISTINCT keyword to concatenate only unique values:

  -- Concatenate values excluding duplicates 
  SELECT STUFF((SELECT DISTINCT ', ' + state FROM cities FOR XML PATH('')), 1, 2, '')

Result:

For more information, see Sybase SQL Anywhere to SQL Server Migration.