Concatenating column values or expressions from multiple rows are usually best done in a client side application language, since the string manipulation capabilities of Transact SQL and SQL based DBMSs are somewhat limited.

The CASE in the recursive part of the CTE is used to eliminate the initial comma, but you can use RIGHT or the SUBSTRING functions instead.

This may not be the best performing option, but certain additional tuning could be done to make them suitable for medium sized datasets.

One such core aspect is the set based nature of SQL expressions (well, multi-sets to be exact, but for the given context let us ignore the issue of duplication).

The primary idea is that tables are unordered and therefore the resultsets of any query that does not have an explicit ORDER BY clause is unordered as well.

Northwind is a sample database in SQL Server 2000 default installations.

You can download a copy from from the Microsoft Downloads When the number of rows is small and almost known beforehand, it is easier to generate the code.When you do not know the number of items that are to be concatenated beforehand, the code can become rather more demanding.The new features in SQL 2005 make some of the approaches easier.It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate.How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a 'aggregate' column?Quite often folks are confused about the potential of breaking relational fundamentals such as the First Normal Form or the scalar nature of typed values.