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.
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 |
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 |
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.