sql-server – 具有多个列聚合的SQL Server数据透视表

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可能会这样做.

有可能获得我想要使用枢轴的记录集,还是有另一个我不知道的工具？

谢谢

解决方法

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 Fiddle with Demo.

如果您的国名不明,则可以使用动态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)

见SQL Fiddle with Demo

两者都给出结果：

|             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 |

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!