Okay look...I know this is 5 years later...but I googled this issue, and this forum post is the #2 item in the google search results.
I just figured out one way to do this, and I thought I would throw it in here for anyone else who also performs the same google search as me.
This won't work for everyone. I have my management studio set to use a fixed width font for my grid results. If you're not using a fixed width font, it will look funky.
You can use STR() to right justify numbers, but if you want to add commas, then that causes issues. And this just occurred to me and it works great:
SELECT REVERSE(CONVERT(CHAR(10), REVERSE(FORMAT(COUNT(*),'N'))))
NOTE: Due to the use of two REVERSE functions and the FORMAT function, this is not recommended for use on queries that will produce a large number of rows. I'm personally using this for a query that outputs a small number of rows (<5000 rows) so for me, the performance hit is not a problem.
I'm adding commas using the FORMAT() function, reversing it, then converting to a CHAR(10) to pad spaces, then reversing again. This will produce a right justified number with commas, but only if you are using a fixed width font on your grid results.
Hope one day this helps someone 🙂