Hot find had to share - SQL concatenation

SuperMad trick i just discovered. COALESC can now eat a fat one.

MAD MAD MAD PROPS to this site.

REFERENCE:http://www.projectdmx.com/tsql/rowconcatenate.aspx
The blackbox XML methods

An example for string concatenation using FOR XML clause with PATH mode is detailed below. It was initially posted by Eugene Kogan later became common in public newsgroups.

    SELECT p1.CategoryId,
         ( SELECT ProductName + ','
             FROM Northwind.dbo.Products p2
            WHERE p2.CategoryId = p1.CategoryId
            ORDER BY ProductName
              FOR XML PATH('') ) AS Products
     FROM Northwind.dbo.Products p1
    GROUP BY CategoryId ;