3

I am using SQL Server 2012. I need to add commas for numbers. For ex 2000 should be 2,000.

How can I do it? Thank you.

Here is my query : 

declare @Term int = 20172;

Select 
    'Applications' as 'NEW FIRST-TIME FRESHMEN',
    Cast(Round(((Cast(y.Last_Term As FLOAT)) * 1), 2) as varchar(10)) as 'Last_Term',
    Cast(Round(((Cast(y.Current_Term AS FLOAT))*1),2) as varchar(10)) as 'Current_Term',
    Cast(Round(((Cast(y.Current_Term AS FLOAT) - Cast(y.Last_Term AS FLOAT)) * 1), 2) as varchar(10)) as '#Change', 
    Cast(Round((((Cast(y.Current_Term AS FLOAT) - Cast(y.Last_Term AS FLOAT)) / (Cast(y.Last_Term AS FLOAT)))* 100), 2) as varchar(10)) + '%' as '%Change'
from
    (Select
         Case 
            When [ACADEMIC_PERIOD_ALL_SF_COMBINED] = @Term 
               Then 'Current_Term'
            When [ACADEMIC_PERIOD_ALL_SF_COMBINED] = @Term-10 
               Then 'Last_Term'
         End ACADEMIC_PERIOD_ALL_SF_COMBINED,
         [APPLICATION_COUNT] Applied
     From
         [IR_DW].[dbo].[vw_dw_Applied_Admitted_Enrolled_F]
     Where 
         STUDENT_LEVEL ='UG'
         and STUDENT_POPULATION ='N'
         and ACADEMIC_PERIOD_ALL_SF_COMBINED between @Term-10 and @Term
         and ACADEMIC_PERIOD_ALL_SF_COMBINED like '%2') x  
Pivot
    (sum(Applied)
       for ACADEMIC_PERIOD_ALL_SF_COMBINED in ([Current_Term] , [Last_Term])
  )y

output

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
gaurav v
  • 63
  • 2
  • 9

2 Answers2

8

Really belongs in the presentation layer, but in 2012+, you can use Format(). It is a great function, but not a high performer.

Example

Select Format(25302.25,'#,##0')

Returns

25,302
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
2

See the similar question here. What you'll do is CAST to MONEY first and then CONVERT to VARCHAR.

Sam W.
  • 33
  • 10