0

I am using SQL Server 2012 and I am running the following T-SQL pivot query:

USE MyDatabase

SELECT PropertyCode, ISNULL([AUGUST 2015],0) AS 'August 2015' FROM
(
 SELECT [PropertyCode], [MTH], [Package Revenue Excl VAT]

 FROM [RSDLIST]) m

 PIVOT (SUM([Package Revenue Excl VAT]) FOR [MTH] IN

 ([AUGUST 2015]

 )) AS PVTTABLE

An extract of my output is shown below:

 PropertyCode   August 2015
    Smith        15071923.019032
    Jones            2152483.164654
    Steeve           7136836.891117

I need to format the second column so that the output becomes:

     PropertyCode     August 2015
        Smith         15,071,923
        Jones     2,152,483
        Steeve    7,136,836

I've had a look at the answers provided here : Format a number with commas but without decimals in SQL Server 2008 R2

and this one from Microsoft: Format (T-SQL)

I've tried the first one by replacing [Package Revenue Excl VAT] at line 3 with the following: CONVERT(varchar,CAST(a.[Package Revenue Excl VAT] AS MONEY),1) AS 'Pkg Rev'

However, since the values have been converted into a varchar, they can't be used in the Pivot query!

Community
  • 1
  • 1
user3115933
  • 4,303
  • 15
  • 54
  • 94
  • run the format code after the numerical pivot. You can use a derived table, CTE or temp table for that purpose. – Jeremy Oct 01 '15 at 18:07

1 Answers1

0
--First
SELECT CONVERT(varchar, CAST(CAST(15071923.019032 as decimal(18,0)) AS money), 1)
-- Second
select replace(convert(varchar,CAST(CAST(15071923.019032 as decimal(18,0)) AS money),1), '.00','')
-- Third
SELECT Format(15071923.019032, '##,##0')

try according your code

select PropertyCode, ISNULL(Format(15071923.019032, '##,##0') ,0) AS 'August 2015' FROM ....
wiretext
  • 3,302
  • 14
  • 19