I wanted to display a changing row-count (in a staging table) including commas (eg: 1,234 or 1,234,567).
I tried using STUFF() in a CASE statement to insert commas, then LEFT(), before settling on this, which is more succinct, and scales from 1 to 1,999,999,999
select replace(convert(varchar(20), convert(money, count(*)), 1), '.00', '') from dbo.sometable with(nolock)
To explain how it works …
– the ‘1’ is a style of VARCHAR that includes commas when converting from MONEY
– then REPLACE removes the ‘.00’ also added during the conversion from MONEY