thanks for the answer.
I don't think is a good idea to run a query for each record. This will consume a lot of resources, and in my case when having to do with large tables and a lot of resulted records this is not an option.
I was hoping there was an internal function, of if it can be defined such a funtion. For example if AVG is used the MSSQL access all the records keep their values and at the end calculates the result. Instead of adding these values I was hoping to create a string which can be compared internally without having to execute another query.
Search the text for "Yep, me too... until I tuned it and then it did it all in 5 seconds flat on a million rows for 50,000 CustID's. " and see if the suggested optimisations are appropriate for you.
Ultimately you are taking relational data and trying to put it into a context that violates first normal form so it is not surprising that SQL does not provide a built in function to do this.
The other alternative is that you can write (if you are using SQL Server 2005) your own CLR aggregate functions. I don't really know anything about these (including how they perform) but you could try researching to see if it is applicable to your needs.
I like the article I linked to as it demonstrates, and compares, two methods of skinning this particular cat. In particular it focuses on performance and, as I pointed out, later on one contributer offers a few refinements that get a decent performance for a medium sized table (1 million rows).
I doubt you will find any alternative technique that will substantially outperform the solution in the article but maybe one of the posters here will surprise me
I want to use this operation in some aggregate complex queries that are already slow. Apart from this in order to get the correct data when calculating these value I must run these queries with other criteria also, such as date range which must be used in order to get the correct records. Therefore it's quite complicated. However I will try it the next days.
I don't think is a good idea to run a query for each record.
Just thought - I don't know how SQL Server optimises the query for this but presumably does as you suggest - runs the function for each record and then groups on the results. So you could improve things (I imagine) with something like:
SELECT Item, dbo.MyConcatFunction(Item) AS CSV_Colors, TotalQuantity
SELECT Item, SUM(Quantity) AS TotalQuantity
GROUP BY Item
) AS Distinct_Items
Call the function once per item rather than once per row.