Jun 5, 2006

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

17 comments:

gabriel said...

excellent.. but what if you want the concatenated results ordered on demand ? how would you use a parameter to the udf to define the ordering ?

Omnibuzz said...

Gabriel,
Forget about ordering based on a parameter. To my knowledge, an order by clause in the query inside the function might not result in proper order, though it most often does, you cannot rely on it. We need to see if someone has come up with a better way of concatenation with ordering :)

Omnibuzz said...

hmmm... detailed explainations? Thats a news to me... I thought that I was too blunt and straight to the point :)

Anonymous said...

I found this whole discussion to be so, so helpful! Thanks!!
Question: As long as we have SQL Server 2005, is there any reason not to use the FOR XML Path('') option I mean, the FOR XML Path('') feature is documented.?. So why would the discussion start by saying that using cursors is the only way to get predictable results? Why couldn't we use the FOR XML option in a production environment? If we don't care about the order of the comma-list, then does that change the discussion? The warnings in this article seem to be about the SQL 2000 'solution'. If I want to take advantage of SQL Server 2005, maybe FOR XML is the hero????
Thanks,
- JJ, Eugene OR

Omnibuzz said...

Hi Eugene
Thanks for your feedback. And coming to your question, The issue is not with SQL Server 2000 or 2005, its more with the approach of having a variable and concatenating it in the select. If you read through the discussion on "Resolution for concatenate column values issues", you will understand the reason why it fails in SQL Server 2000. And for SQL Server 2005, you have two solutions. One is, FOR XML PATH and the other is, CLR aggregate. And the XML method is the only accurate and efficient method in the T-SQL front. But I believe that CLR aggregation might prove to be faster (for concatenation of a larger group) than XML PATH and its definitely a much more cleaner and a reusable solution.
-Omni

Anonymous said...

Thank you for clarifying. That too was most helpful. Happy computing to you.
- JJ, Eugene OR

afree_na said...

Thank you so much.I've been searching for this kind of query for a long time.It helps a lot ^_^

Anonymous said...

Thanx mate it really worked.....

Gordon said...

Fantastic! Thank you so much.

db2 said...

It is useful
Regards,
Jack
http://db2examples.googlepages.com/

Brave Cobra said...

Tnx, this works like a charm.

How can I do this on a temporary table in a stored procedure on SQL2000, since UDF's don't support temporary tables?

Anonymous said...

this is really helpful,thans for the good stuff

carter said...

this is very helpful for me. .

thanks you so much for posting this. . .

M Larsen said...

Umm, the select in your function would start a string with a comma and space if your variable is null. This would look silly.

You would have to revise it to:


select @resultStr = coalesce(@resultStr+',','')+ Col2 from StrConcat where col1 = @Col1

Anonymous said...

very useful. how do you include more than one column in the select prior to joining the colums

Anonymous said...

thank's very much. my problem solved.

Choox said...

Thank you very much.. Really appreciate this

Post a Comment