SQL, SQL Server, T-SQL, Technical

Concatenating column values in SQL Server 2000/2005

If you want to concatenate values from a specific column like for example:

Table
=====
1    One
2    Two
3    Three
4    Four

to the following:

Output
======
One, Two, Three, Four

Here is a simple way to do it:

SQL Server 2005:

SELECT STUFF(SELECT ‘, ‘ + ColumnName FROM TableName FOR XML PATH(”),1,2,”)

SQL Server 2000:

DECLARE @t varchar(4000)
SET @t = ”
SELECT @t = @t + ‘, ‘ + ColumnName From TableName
SELECT STUFF(@t,1,2,”)

STUFF() deletes a specified length of characters and inserts another set of characters at a specified starting point.

Advertisements

2 thoughts on “Concatenating column values in SQL Server 2000/2005

    1. Its working…and Below is correct query

      SELECT STUFF((SELECT ‘, ‘ + ColumnName FROM TableName FOR XML PATH(”)),1,2,”)

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