Format Sql Server 2005 Query Results 2

[BxWill]

Displayed below is a portion of the sql that is used in Sql Server 2005 to extract data from a Sql Server database.

Upon running the query, the results are not formatted with any decimal points or commas...

What modifications are needed so that the query results are formatted with a comma at the thousands place and there
only 2 digits after the decimal point?

,SUM (BT.PROCESS_ALLOC_CHRG_AMT)AS ALLOC_CHRG
,SUM (BT.PROCESS_ALLOC_PAID_AMT) AS ALLOC_PD
,SUM (BT.PROCESS_CHRG_AMT) AS CHARGES
,SUM (BT.PROCESS_PAID_AMT) AS PAID
,SUM (BT.PROCESS_DAY_CNT) AS DAYS
,SUM (BT.PROCESS_ENCOUNTER_CNT) AS ADMITS

,CASE WHEN SUM(BT.PROCESS_ALLOC_CHRG_AMT) =0 OR SUM(BT.PROCESS_ENCOUNTER_CNT) = 0 THEN 0 ELSE SUM(BT.PROCESS_ALLOC_CHRG_AMT)/SUM(BT.PROCESS_ENCOUNTER_CNT) END ChgPerAdmit

,CASE WHEN SUM (BT.PROCESS_ALLOC_PAID_AMT) =0 OR SUM(BT.PROCESS_ENCOUNTER_CNT) = 0 THEN 0 ELSE SUM (BT.PROCESS_ALLOC_PAID_AMT)/SUM (BT.PROCESS_ENCOUNTER_CNT) END PaidPerAdmit

,CASE WHEN SUM(BT.PROCESS_DAY_CNT) =0 OR SUM(BT.PROCESS_ENCOUNTER_CNT) = 0 THEN 0 ELSE SUM (BT.PROCESS_DAY_CNT)/SUM (BT.PROCESS_ENCOUNTER_CNT) END ALOS

,CASE WHEN SUM (BT.PROCESS_ALLOC_CHRG_AMT) =0 OR SUM(BT.PROCESS_DAY_CNT) = 0 THEN 0 ELSE SUM (BT.PROCESS_ALLOC_CHRG_AMT)/SUM (BT.PROCESS_DAY_CNT) END AllocChrgPerDay

,CASE WHEN SUM (BT.PROCESS_ALLOC_PAID_AMT) =0 OR SUM(BT.PROCESS_DAY_CNT) = 0 THEN 0 ELSE SUM (BT.PROCESS_ALLOC_PAID_AMT)/SUM (BT.PROCESS_DAY_CNT) END AllocPaidPerDay

,CASE WHEN SUM (BT.PROCESS_ALLOC_PAID_AMT) =0 OR SUM (BT.PROCESS_ALLOC_CHRG_AMT) = 0 THEN 0 ELSE SUM (BT.PROCESS_ALLOC_PAID_AMT)/SUM (BT.PROCESS_ALLOC_CHRG_AMT) END PaidPercentage

 

[gmmastros]

it depends on the data types of your columns. If you are using the money data type, then a simple convert to varchar with a style = 1 will suffice. If the data type is anything other than money, you will need to convert twice, like this:

Select Convert(VarChar(20), Convert(Money, 12345678.90), 1)

Of course, when putting this in to your query, the SUM(column) needs to go where I hard coded the value in the code sample above.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Just to add to George's response. Usually formatting is done on the Client and not in SQL. What you're using to display the returned data?

PluralSight Learning Library

 

[BxWill]

What was initially posted is portion of the sql script that is within the query pane of Sql Server Management Studio (Sql Server 2005).

I specify that the query results save to a file on a network drive. Then, I open a Excel Workbook and use Data/Get External Data...

After I import the data into a Excel Worksheet, I have to manually format the columns to display the comma at the thousands place and to not display no more than 2 digits after the decimal point.

Is it possible to specify a "format" standard at the beginning of the sql script?

Something like - "Dim Alloc Chg Amt currency 9999.99

 

[markros]

If you cast that column to Money, how does it display in Excel?

PluralSight Learning Library

 

[markros]

Coincidentally, today's re-activation of the old thread on this same topic on MSDN T-SQL forum with the interesting CLR function posted

MSDN thread

PluralSight Learning Library