SQL Tips by Namwar Rizvi

April 30, 2007

Generating a comma separated list by an SQL Query

Filed under: comma seprated list,string concatenation — namwar @ 9:42 PM

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’

Advertisements

1 Comment »

  1. There are known problems with the above syntax. I believe you should make your readers aware of them.

    See this example for details:
    http://milambda.blogspot.com/2005/07/return-related-values-as-array.html

    ML

    Comment by Matija Lah — May 9, 2007 @ 1:46 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: