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.

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)

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', '')

For SQL Server 2012+ implementations, you will have the ability to use the FORMAT to apply string formatting to non-string data types.

In the original question, the user had requested the ability to use commas as thousands separators. In a closed as duplicate question, the user had asked how they could apply currency formatting. The following query shows how to perform both tasks. It also demonstrates the application of culture to make this a more generic solution (addressing Tsiridis Dimitris's function to apply Greek special formatting)

-- FORMAT
-- http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx
-- FORMAT does not do conversion, that's the domain of cast/convert/parse etc
-- Only accepts numeric and date/time data types for formatting. 
--
-- Formatting Types
-- http://msdn.microsoft.com/en-us/library/26etazsy.aspx

-- Standard numeric format strings
-- http://msdn.microsoft.com/en-us/library/dwhawy9k.aspx
SELECT
    -- c => currency
    -- n => numeric
    FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
    (
        -- Language culture names
        -- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
        VALUES
            ('en-US')
        ,   ('en-GB')
        ,   ('ja-JP')
        ,   ('Ro-RO')
        ,   ('el-GR')
    ) C (culture);

SQLFiddle for the above