SQL Server Pivot Table with multiple column aggregates
I've got a table:
create table mytransactions(country varchar(30), totalcount int, numericmonth int, chardate char(20), totalamount money)
The table has these records:
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 36, 7, 'Jul-12', 699.96)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 44, 8, 'Aug-12', 1368.71)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 52, 9, 'Sep-12', 1161.33)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 50, 10, 'Oct-12', 1099.84)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 38, 11, 'Nov-12', 1078.94)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 63, 12, 'Dec-12', 1668.23)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 11, 7, 'Jul-12', 257.82)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 5, 8, 'Aug-12', 126.55)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 7, 9, 'Sep-12', 92.11)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 12, 10, 'Oct-12', 103.56)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 21, 11, 'Nov-12', 377.68)
Go
insert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Austria', 3, 12, 'Dec-12', 14.35)
Go
This is what a select * looks like:
Country TotalCount numericmonth chardate totalamount
--------- ---------- ----------- -------- -----------
Australia 36 7 Jul-12 699.96
Australia 44 8 Aug-12 1368.71
Australia 52 9 Sep-12 1161.33
Australia 50 10 Oct-12 1099.84
Australia 38 11 Nov-12 1078.94
Australia 63 12 Dec-12 1668.23
Austria 11 7 Jul-12 257.82
Austria 5 8 Aug-12 126.55
Austria 7 9 Sep-12 92.11
Austria 12 10 Oct-12 103.56
Austria 21 11 Nov-12 377.68
Austria 3 12 Dec-12 14.35
I want to pivot this record set so it looks like this:
Australia Australia Austria Austria
# of Transactions Total $ amount # of Transactions 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
This is the pivot code I've come up with so far:
select * from mytransactions
pivot (sum (totalcount) for country in ([Australia], [Austria])) as pvt
This is what I'm getting:
numericmonth chardate totalamount Australia Austria
----------- -------- ---------- --------- -------
7 Jul-12 257.82 NULL 11
7 Jul-12 699.96 36 NULL
8 Aug-12 126.55 NULL 5
8 Aug-12 1368.71 44 NULL
9 Sep-12 92.11 NULL 7
9 Sep-12 1161.33 52 NULL
10 Oct-12 103.56 NULL 12
10 Oct-12 1099.84 50 NULL
11 Nov-12 377.68 NULL 21
11 Nov-12 1078.94 38 NULL
12 Dec-12 14.35 NULL 3
12 Dec-12 1668.23 63 NULL
I can manually aggregate the records in a table variable loop, however it seems that pivot might be able to do this.
Is is possible to get the record set I want using pivot or is there another tool that I'm not aware of?
Thanks
Answer
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 |