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

I think, it's dbo.Concatenate function which should perform this sorting.|||

You can't do this using SQLCLR UDFs right now. The ORDER BY in the derived table only applies to that scope. And there is no guarantee that rows/values will be sent in sorted order to the UDF within each group. You could do the sorted concatenation using a SQL technique like below:

select pt.ColA, pt.[1] + coalesce(',' + pt.[2], '') + coalesce(',' + pt.[3], '') as Aggr

from (

select ColA, ColB, ROW_NUMBER() OVER(PARTITION BY Col1A ORDER BY ColB) as seq

from tbl

) as t

pivot (min(ColB) for seq in ([1], [2], [3] /* as many items per group that you want to support */)) as pt