LIST Aggregate Function - Sybase SQL Anywhere to SQL Server Migration

In Sybase SQL Anywhere (Sybase ASA), LIST aggregate function concatenates strings from multiple rows into single string with the specified delimiter.

In Microsoft SQL Server, you can use FOR XML PATH clause of SELECT statement.

LIST Function in Sybase SQL Anywhere

Let's create the following sample tables:

Sybase SQL Anywhere:

  CREATE TABLE states
  (
      abbr CHAR(2),
      name VARCHAR(70)
  );
 
  INSERT INTO states VALUES ('CA', 'California');
  INSERT INTO states VALUES ('MA', 'Massachusetts');
  INSERT INTO states VALUES ('NY', 'New York');
 
  CREATE TABLE cities
  (
      name VARCHAR(70),
      state CHAR(2)
  );
 
  INSERT INTO cities VALUES ('San Francisco', 'CA');
  INSERT INTO cities VALUES ('Los Angeles', 'CA');
  INSERT INTO cities VALUES ('Boston', 'MA');
  INSERT INTO cities VALUES ('New York', 'NY');

Now using LIST aggregate function you can concatenate values as follows:

  -- Concatenate values from all rows into single delimited value 
  SELECT LIST(name, ', ') FROM cities;

Result:

name
San Francisco, Los Angeles, Boston, New York

LIST function concatenates values within a group:

  -- Concatenate values from all rows within a group 
  SELECT LIST(name, ', ') FROM cities 
  GROUP BY state;

Result:

name
San Francisco, Los Angeles
Boston
New York

By default , LIST function concatenates all values and the same value can be specified multiple times:

  -- Concatenate values allowing duplicates
  SELECT LIST(state, ', ') FROM cities

Result:

name
CA, CA, MA, NY

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

  -- Concatenate values excluding duplicates 
  SELECT LIST(DISTINCT state, ', ') FROM cities

Result:

name
CA, MA, NY

You can use LIST function with joins:

  -- Concatenate values  
  SELECT LIST(DISTINCT s.name, ', ') unique_name, LIST(s.name, ', ') name
  FROM cities c, states s
  WHERE c.state = s.abbr;

Result:

unique_name name
California, Massachusetts, New York California, California, Massachusetts, New York

Implementing LIST Function in SQL Server

In Microsoft SQL Server you can use FOR XML XPATH clause of 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 cities FOR XML PATH('')

Result:

name
<name>San Francisco</name><name>Los Angeles</name><name>Boston</name><name>New York</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 cities FOR XML PATH('')

Result:

name
, San Francisco, Los Angeles, Boston, New York

Single column in SELECT list and without GROUP BY

The previous query returns redundant ', ' before the first value that can be removed using STUFF function:

  -- Implementing LIST for single column  
  SELECT STUFF((SELECT ', ' + name FROM cities FOR XML PATH('')), 1, 2, '')

Result:

name
San Francisco, Los Angeles, Boston, New York

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:

name
San Francisco, Los Angeles
Boston
New York

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:

name
CA, CA, MA, NY

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:

name
CA, MA, NY

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - August 2013.

You could leave a comment if you were logged in.