How to format a number

Transact-SQL
1

Hello,

I have the following query.

SELECT DISTINCT N, SUM( CASE WHEN B = 0 THEN C ELSE -B END) * -1 AS My_Number
FROM  TB1 INNER JOIN TB2 ON TB1.CD = TB2.AT
GROUP BY N;

This query generates the following output.

N   My_Number
1   509987.070120
2   222861.234150
3   210986.838900

How can I get this formatted output? Notice: 1) no more decimals 2) numbers are rounded 3) commas added.

N   My_Number
1   509,987
2   222,861
3   210,987

Thank you for your help.

2

This type of formatting should be done in the application and not in TSQL. Return the raw data to the application and format there.

1 Like
3

To add to the first reply, SQL is based on a tiered architecture. The database tier handles all of the database retrieval and data integrity. But nothing else. The data display and formatting is done in presentation layers that get data from the database layer.

What you want is 1960's COBOL in which there a single monolithic program that does computations, data retrieval and display. Look up the PICTURE clause in the DATA DIVISION of a COBOL Program. At this point in your career, you are not fit to do RDBMS; please stop and get an education so your can make better errors and not conceptual :blush: ones like this.

4

I agree with Tara, this should be done in application layer. This type of formatting can be easily done any reporting tool.

5

Hi

CREATE TABLE #TEMP (N int , My_Number float)

insert into #temp values (1, 509987.070120)
insert into #temp values (2 , 222861.234150)
insert into #temp values (3, 210986.838900)

select n,my_number from #temp

select n,format(my_number,'###,###,###') my_number from #temp

6

Hello,

Tara, Mangal - After some research, now I understand your point. Thanks for your time.

Jcelko - I am new in SQL Server and would take you advise. Thanks.

7

Just so you know, FORMAT is 44 times slower than most anything else that you could throw at this problem. MS did us no favors when they coded the FORMAT function. It would be faster to use CONVERT in this case.