Tabela przestawna programu SQL Server z wieloma agregacjami kolumn
Mam stolik:
create table mytransactions(country varchar(30), totalcount int, numericmonth int, chardate char(20), totalamount money)
Tabela zawiera następujące rekordy:
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
Oto jak wygląda opcja *:
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
Chcę przestawić ten zestaw rekordów tak, aby wyglądał tak:
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
To jest kod przestawny, który do tej pory wymyśliłem:
select * from mytransactions
pivot (sum (totalcount) for country in ([Australia], [Austria])) as pvt
Oto co otrzymuję:
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
Mogę ręcznie agregować rekordy w pętli zmiennych tabeli, jednak wydaje się, że może to zrobić pivot.
Czy możliwe jest uzyskanie zestawu rekordów za pomocą przestawienia, czy też jest inne narzędzie, którego nie znam?
Dzięki