I would do this slightly different by applying both the UNPIVOT and the PIVOT functions to get the final result. The unpivot takes the values from both the totalcount and totalamount columns and places them into one column with multiple rows. You can then pivot on those results.:
select chardate,
Australia_totalcount as [Australia # of Transactions],
Australia_totalamount as [Australia Total $ Amount],
Austria_totalcount as [Austria # of Transactions],
Austria_totalamount as [Austria Total $ Amount]
from
(
select
numericmonth,
chardate,
country +'_'+col col,
value
from
(
select numericmonth,
country,
chardate,
cast(totalcount as numeric(10, 2)) totalcount,
cast(totalamount as numeric(10, 2)) totalamount
from mytransactions
) src
unpivot
(
value
for col in (totalcount, totalamount)
) unpiv
) s
pivot
(
sum(value)
for col in (Australia_totalcount, Australia_totalamount,
Austria_totalcount, Austria_totalamount)
) piv
order by numericmonth
See SQL Fiddle with Demo.
If you have an unknown number of country names, then you can use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@colsName AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(country +'_'+c.col)
from mytransactions
cross apply
(
select 'TotalCount' col
union all
select 'TotalAmount'
) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsName
= STUFF((SELECT distinct ', ' + QUOTENAME(country +'_'+c.col)
+' as ['
+ country + case when c.col = 'TotalCount' then ' # of Transactions]' else 'Total $ Amount]' end
from mytransactions
cross apply
(
select 'TotalCount' col
union all
select 'TotalAmount'
) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'SELECT chardate, ' + @colsName + '
from
(
select
numericmonth,
chardate,
country +''_''+col col,
value
from
(
select numericmonth,
country,
chardate,
cast(totalcount as numeric(10, 2)) totalcount,
cast(totalamount as numeric(10, 2)) totalamount
from mytransactions
) src
unpivot
(
value
for col in (totalcount, totalamount)
) unpiv
) s
pivot
(
sum(value)
for col in (' + @cols + ')
) p
order by numericmonth'
execute(@query)
Both give the result:
| CHARDATE | AUSTRALIA # OF TRANSACTIONS | AUSTRALIA TOTAL $ AMOUNT | AUSTRIA # OF TRANSACTIONS | AUSTRIA TOTAL $ AMOUNT |
--------------------------------------------------------------------------------------------------------------------------------------
| Jul-12 | 36 | 699.96 | 11 | 257.82 |
| Aug-12 | 44 | 1368.71 | 5 | 126.55 |
| Sep-12 | 52 | 1161.33 | 7 | 92.11 |
| Oct-12 | 50 | 1099.84 | 12 | 103.56 |
| Nov-12 | 38 | 1078.94 | 21 | 377.68 |
| Dec-12 | 63 | 1668.23 | 3 | 14.35 |