I found this function a couple of weeks ago and found it very usful. I had to add a filed to a report, that contained a list of other fileds from the report, each seperated by a comma. At first i thought i’d have to write some custom code, but then I found the COALESCE function.
You can use this in the following way, to create one output from x number of records, seperated by a comma.

DECLARE @aa varchar(100)

SELECT @aa = COALESCE (@aa + ‘, ‘, ”) + CAST(name AS varchar(5))
FROM tbl_Customer

WHERE (Customer_bal > 10)
SELECT @aa AS Names

I then added this sql to a sub report and passed the id of the line to it, so I could then have this showing for each record. The time it took to render the report increased, but the user got the information they needed.
I then had to write some custom code into the report so it would sort the results in a predictable way. You can read more about the function here