Concatenate the values in a column in SQL Server 2000 and 2005

Please Note: Before you go through this article, the approach I have given for SQL Server 2000 is unpredicatable and might fail. The only predictable way is to use a cursor and loop through it and concatenate the values. Please go through the links I had given in the post to understand the potential issues. I would suggest you not to use this approach in production environment. The suggestions for SQL Server 2005 (both XML and CLR), however, shouldn't have any problem.
-Omni
---------------------------------------------------------------

This is the most common question that comes in most discussion forums that I feel its high time Microsoft brings in an inbuilt function for it.

"How do I concatenate the values (comma seperated) in a column and group it by another column?"

We don't have any in-built function to do this. But, here are a few approaches in SQL Server 2000 and in 2005.

Let me set up the data first before I dive in to the query.


create table StrConcat (col1 nvarchar(10),col2 nvarchar(10))
go
insert into StrConcat
select 'db1','host1'
union all select 'db1','host2'
union all select 'db1','host3'
union all select 'db2','host1'
union all select 'db2','host2'
union all select 'db3','host2'
union all select 'db3','host3'

To tell you the truth the table definition and sample data was flicked from an OP in a discussion forum. Thanks buddy. May god bless you.

The result that I want is something like this


col1 col2
---------- -----------------
db1 host1,host2,host3
db2 host1,host2
db3 host2,host3

In SQL Server 2000, I cannot do it in a single query. I need to use a function.

Let me create the function first...

create function Concat (@Col1 varchar(10))
returns varchar(1000)
as
begin
declare @resultStr varchar(1000)
select @resultStr = coalesce(@resultStr,'') + ','+ Col2 from StrConcat where col1 = @Col1
return stuff(@resultstr,1,1,'')
end

Now the query is simple.


Select Col1,dbo.Concat(col1) from StrConcat
group by col1
order by col1

The above query works fine for my example but before you extend it to your table you would want to go through this link too.

funny behavior in sql server 2000 udf
(Thanks to David Portas for pointing this issue out to me)

You should also take a look at this link before you start implementing it, since there are some potential pitfalls you must know.

Resolution for concatenate column values issues
(Thanks to Anith Sen for pointing this issue out to me)


In SQL Server 2005, we can do this without the UDF.
The query goes this way...


select col1, stuff( ( select ','+ col2
from StrConcat t1
where t2.col1 = t1.col1
for xml path('')
),1,1,'')
from StrConcat t2
group by col1
order by col1

You may also try to create a CLR aggregate to do the job. Check this link on how to go about doing it.

String Concatenation Example