How do we can format money or decimal with commas?

Sometimes we want to have our money fields properly formatted with commas like this: 12,345,678.9999

For the same purpose we can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that style selection

Here is an example:

DECLARE @value MONEYSELECT @v = 12345678.6666
SELECT CONVERT(VARCHAR,@value ,0) --12345678.67 value is Rounded but no formatting is done
SELECT CONVERT(VARCHAR,@value ,1) --12,345,678.67 Formatted with commas
SELECT CONVERT(VARCHAR,@value ,2) --12345678.6666 No formatting

Now If we have a decimal field it doesn't work with the convert function. The work around in this case is to convert it to money and then follow the above method.

For example:

DECLARE @TargetValue DECIMAL (36,10)
SELECT @TargetValue = 12345678.6666
SELECT CONVERT(VARCHAR,CONVERT(MONEY,@TargetValue ),1) --12,345,678.67 Formatted with commas