我有一张桌子：
create table mytransactions(country varchar(30),totalcount int,numericmonth int,chardate char(20),totalamount money)
该表有这些记录：
insert into mytransactions(country,totalcount,numericmonth,chardate,totalamount) values('Australia',36,7,'Jul-12',699.96)
Go
insert into mytransactions(country,44,8,'Aug-12',1368.71)
Go
insert into mytransactions(country,52,9,'Sep-12',1161.33)
Go
insert into mytransactions(country,50,10,'Oct-12',1099.84)
Go
insert into mytransactions(country,38,11,'Nov-12',1078.94)
Go
insert into mytransactions(country,63,12,'Dec-12',1668.23)
Go
insert into mytransactions(country,totalamount) values('Austria',257.82)
Go
insert into mytransactions(country,5,126.55)
Go
insert into mytransactions(country,92.11)
Go
insert into mytransactions(country,103.56)
Go
insert into mytransactions(country,21,377.68)
Go
insert into mytransactions(country,3,14.35)
Go
这是一个select *的样子：
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
我想要把这个记录设置为这样：
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
这是我到目前为止提供的枢纽代码：
select * from mytransactions pivot (sum (totalcount) for country in ([Australia],[Austria])) as pvt
这是我得到的：
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
我可以手动聚合表变量循环中的记录,但是似乎pivot可能会这样做.
有可能获得我想要使用枢轴的记录集,还是有另一个我不知道的工具？
谢谢
解决方法
我将通过应用UNPIVOT和PIVOT函数来获得最终结果来做到这一点. unpivot会从totalcount和totalamount列中获取值,并将它们放入具有多行的一列.然后,您可以透视这些结果：
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,country +'_'+col col,value
from
(
select numericmonth,country,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
如果您的国名不明,则可以使用动态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,'')
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(''),'')
set @query
= 'SELECT chardate,' + @colsName + '
from
(
select
numericmonth,country +''_''+col col,value
from
(
select numericmonth,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)
两者都给出结果：
| 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 |
