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

2021-12-02 00:00:00 pivot sql-server-2005 sql-server unpivot

我有一张桌子:

 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 * 的样子:

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

这是我得到的:

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 可以做到这一点.

I can manually aggregate the records in a table variable loop, however it seems that pivot might be able to do this.

是否可以使用pivot获取我想要的记录集,或者还有其他我不知道的工具吗?

Is is possible to get the record set I want using pivot or is there another tool that I'm not aware of?

谢谢

推荐答案

我会通过应用 UNPIVOTPIVOT 函数来获得最终结果结果.unpivot 从 totalcounttotalamount 列中获取值,并将它们放入具有多行的一列中.然后,您可以根据这些结果进行调整.:

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

参见SQL Fiddle with Demo.

如果你有未知数量的country名称,那么你可以使用动态SQL:

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)

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

相关文章