Concatentate Sorted Strings

Hi All,

I'm new to SQL Server.

I have the following table in a database of SQL Server 2005 Express.

ColA ColB
----- -----
A 12
A 10
B 50
B 13

What I want to achieve is the following result :

Col A Aggr
----- ------
A 2,12
B 13, 50

I tried the following query :

SELECT ColA, dbo.Concatenate(ColB) as Aggr
FROM
(SELECT TOP(100) PERCENT ColA, ColB
FROM TABLE_A
ORDER BY ColB) AS Derived_A
GROUP BY ColA

where Concatenate is the CLR User-defined aggreate function written in C#.

What I want to do is to first sort the rows on ColB and then to execute aggregate function over the sorted rows so as to get the above-mentioned results. However, what I got is the following

Col A Aggr

----- ------

A 2,12

B 50,13 -- not sorted

I learnt from the SQL Server documentation that even though the ORDER BY clause is presented in the subquery, the query result is not guaranteed to be sorted. Only ORDER BY clause used in the outer query should work. So, how should this problem be solved? One way of which I'm thinking is to do the sorting in the aggregate function, but I'm worrying if this is harmful to the performance. Could anyone help me to solve the problem?

Thanks!

Regards,
Nathan