In response to Jamie Thomson's T-SQL poser, here's one very efficient method of concatenation in an aggregate query. It uses the FOR XML clause to turn a result set into a string.
SELECT DISTINCT id,
SUBSTRING(
(SELECT ','+name AS [text()]
FROM t1
WHERE id = T.id
ORDER BY name
FOR XML PATH( '' )
), 2,100) AS concat
FROM t1 AS T;
This solution is due to Itzik Ben-Gan and others and is described in Inside SQL Server 2005. The main disadvantage is that special characters such as "<" will get delimited with XML-style quoting in the result.