A more concise way to format COUNT result with thousands separator?

Question

Goal

Format a COUNT result to a common format (e.g. ###,##0) in a concise statement and without UDF's.

I've Tried

I am currently using something like this, though it leaves two decimals and is clunky:

SELECT CONVERT(VARCHAR, CAST(COUNT([id]) AS MONEY), 1) FROM tbl

The reason I went that direction is because it was the only standard formatting option I could find when reading through the CAST and CONVERT documentation from MSDN.

I don't really like it, but it limps along since these numbers are simply copied and pasted into other text editors and such after the T-SQL runs. They do of course have to be formatted by hand to remove the decimals in some places.

Research and Restrictions

Of course you could build a user-defined function, like this one on this blog, but I have a restriction that keeps me from building UDF's for this purpose.

After some additional research I found that if I were using SQL 2012 I could use a new T-SQL FORMAT function, alas, I'm restricted to 2008 R2. This of course leverages a different platform as it's a .NET interface. :D

I am also aware of this solution: Format a number with commas but without decimals in SQL Server 2008 R2?

Answer 1

Even though the question author was stuck on SQL Server 2008R2, this is a concise and efficient solution should you be blessed with SQL Server 2012 or newer.

No digits after decimal point (rounded)

SELECT FORMAT(COUNT([id]), N'N0')
FROM [tbl]

Example output

53,234,568

One digit after decimal point

SELECT FORMAT(COUNT([id]), N'N1')
FROM [tbl]

Example output

53,234,567.9

Two digits after decimal point

SELECT FORMAT(COUNT([id]), N'N2')
FROM [tbl]

Example output

53,234,567.89

(etc.)

Two digits after decimal point, no thousands separator

SELECT FORMAT(COUNT([id]), N'#.00')
FROM [tbl]

Example output

53234567.89