How do I format a number with commas in T-SQL?

In SQL Server 2012 and higher, this will format a number with commas:

select format([Number], 'N0')

You can also change 0 to the number of decimal places you want.

I'd recommend Replace in lieu of Substring to avoid string length issues:

REPLACE(CONVERT(varchar(20), (CAST(SUM(table.value) AS money)), 1), '.00', '')

While I agree with everyone, including the OP, who says that formatting should be done in the presentation layer, this formatting can be accomplished in T-SQL by casting to money and then converting to varchar. This does include trailing decimals, though, that could be looped off with SUBSTRING.

SELECT CONVERT(varchar, CAST(987654321 AS money), 1)