Parade: SQL Server 2005 Database list total

server| Data | database

A common problem in Microsoft's SQL Server Forum is whether SQL Server 2005 has aggregate functions similar to sum, but is used for collections of strings. For example, suppose a database has the following tables and data:

CREATE TABLE Strings
(
String VARCHAR (20)
)

INSERT Strings VALUES (' A ')
INSERT Strings VALUES (' B ')
INSERT Strings VALUES (' C ')

It is possible to use the following collection on this table to generate a list of strings:
SELECT Listagg (String)
From Strings

Output:

' A, B, C '

Although this total is not built into SQL Server 2005, the new system still introduces a way to easily achieve this functionality. The most common way is to use the new CLR user-defined totals (UDA). Unfortunately, user-defined totals have a limit of 8,000 bytes, which greatly limits their total application to large collections.

Another way to achieve a goal in SQL Server 2005 is a by-product of the new for XML path function. By making an empty path, it is possible to produce a function that resembles string totals:

SELECT String + ', ' as [text ()]

From Strings

ORDER BY String

For XML PATH (')

More information about this technology can be found on the Aaron Bertrand asp FAQ http://www.aspfaq.com/ website.