Aggregate concatenation in SQL Server 2005

In one of my previous posts, I talked about generating a comma separated list in TSQL. One of my readers has pointed out some interesting facts about issues related to this approach.

Following are the issues documented at MSDN

Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

Expression in ORDER BY clause causes self-referencing variables in the SELECT clause to evaluate only once rather than once per row

So what should we do now?
An alternative approach is to use For XML to generate comma separated list. Full working example is available at:

http://milambda.blogspot.com/2005/07/return-related-values-as-array.html
and
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx

Generating a comma separated list by an SQL Query

Today I am showing you a quick and easy way to concatenate rows of textual data in a single string. This is particularly useful when you need a comma separated list of items, products etc. and you don’t want to loop the whole dataset for performance reasons.

Following is the sample code to demonstrate this. Concept is very simple and anyone can easily understand it by running the following code.

Set Nocount On
Print ‘Start’
Print ‘Creating Sample Items table’
Go
CREATE TABLE dbo.tblItems
(
ItemID int NOT NULL IDENTITY (1, 1),
ItemName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.tblItems ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
( ItemID )
GO
Print ‘Inserting data in sample table’
Go

Insert into dbo.tblItems (ItemName) Values (‘Item1′)
Insert into dbo.tblItems (ItemName) Values (‘Item2′)
Insert into dbo.tblItems (ItemName) Values (‘Item3′)
Insert into dbo.tblItems (ItemName) Values (‘Item4′)
Go
Print ‘Following is the comma separated list of Items’
Go
Declare @m_ItemsList nvarchar(2000)
Select @m_ItemsList=”
Select @m_ItemsList=@m_ItemsList+ItemName+‘,’ from tblItems
Select Left(@m_ItemsList,Len(@m_ItemsList)-1)
Go
Print ‘Dropping sample table’
Drop table dbo.tblItems
Go
Print ‘Finished’