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

Question

我有一张 table :

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

这就是 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

我可以手动聚合表变量循环中的记录，但似乎枢轴可能能够做到这一点。

是否可以使用数据透视表获取我想要的记录集，或者是否还有其他我不知道的工具？

谢谢

Answer 1

我会通过应用 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, 
    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

参见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,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)

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