Hi
Many times you come across a situation where you have to convert values stored in multiple rows in a comma separated string e.g. Part consumed in a job.
Using Coalesce you can achieve the same but the drawback is that it would hit the performance significantly if you need to call this function for each row in a recordset. Actually using any function when it needs to be applied to each row will hit query performance unless the rows returned are not too many !
As an alternate to Coalesce, you can use For XML construct which will give better performance if your recordset is large.
Sample :
SELECT t1.ID ,
SUBSTRING(( SELECT ( ‘ ,’ + t2.Part )
FROM dbo.PartConsumed t2
WHERE t1.ID = t2.ID
FOR
XML PATH(”)
), 3, 500) AS PartUsed
FROM dbo.PartConsumed t1
GROUP BY t1.ID
