具有多列聚合的SQL Server Pivot表

I've got a table:

我有一个表:

 create table mytransactions(country varchar(30), totalcount int, numericmonth int, chardate char(20), totalamount money)

The table has these records:

该表有以下记录:

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

This is what a select * looks like:

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

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

This is what I'm getting:

这就是我得到的:

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

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?

是否有可能使用pivot来获取我想要的记录集，还是有其他我不知道的工具?

Thanks

谢谢

4 个解决方案

#1

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

通过同时应用主元和主元函数来得到最终的结果，我的做法稍微有些不同。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

See SQL Fiddle with Demo.

参见SQL小提琴演示。

If you have an unknown number of country names, then you can use dynamic SQL:

如果您有未知数量的国家名称，那么您可以使用动态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)

See SQL Fiddle with Demo

参见SQL小提琴演示

Both give the result:

都给的结果:

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

#2

I have added the dynamic query/solution.

我添加了动态查询/解决方案。

Static

静态

SELECT  t.chardate,
        SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions",
        SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount",
        SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions",
        SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount"
FROM    mytransactions t
GROUP BY t.chardate;

Note:

注意:

1) ORDER BY t.chardate will not work because the values from chardate column are chars.

1)按t。chardate不能工作，因为来自chardate列的值是chars。

2) My advice is to split chardate in two columns numericmonth and numericyear. In this last case, you could use this solution:

我的建议是把chardate分成数字月和数字年两栏。在最后一种情况下，您可以使用以下解决方案:

SELECT  t.numericyear, t.numericmonth,
        SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions",
        SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount",
        SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions",
        SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount"
FROM    mytransactions t
GROUP BY t.numericyear, t.numericmonth
ORDER BY BY t.numericyear, t.numericmonth;

Dynamic

动态

DECLARE @Sql NVARCHAR(MAX)='SELECT t.chardate';
DECLARE @ColumnTemplate NVARCHAR(MAX)='SUM(CASE WHEN t.country=''{country}'' THEN t.totalcount ELSE 0 END) AS "{country} # of Transactions"
,SUM(CASE WHEN t.country=''{country}'' THEN t.totalamount ELSE 0 END) AS "{country} Total $ amount"'

SELECT @Sql=@Sql+CHAR(13)+','+REPLACE(@ColumnTemplate, '{country}', REPLACE(c.name,'''','''''')e)
FROM (
    SELECT  DISTINCT t.country AS name
    FROM    mytransactions t
) c

SELECT @Sql=@Sql+'
FROM mytransactions t
GROUP BY t.chardate;'

PRINT @Sql;


EXEC(@Sql);

Results:

结果:

SELECT t.chardate
,SUM(CASE WHEN t.country='Australia' THEN t.totalcount ELSE 0 END) AS "Australia # of Transactions"
,SUM(CASE WHEN t.country='Australia' THEN t.totalamount ELSE 0 END) AS "Australia Total $ amount"
,SUM(CASE WHEN t.country='Austria' THEN t.totalcount ELSE 0 END) AS "Austria # of Transactions"
,SUM(CASE WHEN t.country='Austria' THEN t.totalamount ELSE 0 END) AS "Austria Total $ amount"
FROM mytransactions t
GROUP BY t.chardate;

Note: The REPLACE function from SELECT @Sql=@Sql+CHAR(13)+ ... REPLACE(c.name,'''','''''')) is used to prevent SQL injections.

注意:SELECT @Sql=@Sql+CHAR(13)+…REPLACE(c.name， " "， " " ")用于防止SQL注入。

#3

I used your own pivot as a nested query and came to this result:

我使用您自己的pivot作为嵌套查询，得到以下结果:

SELECT
  [sub].[chardate],
  SUM(ISNULL([Australia], 0)) AS [Transactions Australia],
  SUM(CASE WHEN [Australia] IS NOT NULL THEN [TotalAmount] ELSE 0 END) AS [Amount Australia],
  SUM(ISNULL([Austria], 0)) AS [Transactions Austria],
  SUM(CASE WHEN [Austria] IS NOT NULL THEN [TotalAmount] ELSE 0 END) AS [Amount Austria]
FROM
(
  select * 
  from  mytransactions
  pivot (sum (totalcount) for country in ([Australia], [Austria])) as pvt
) AS [sub]
GROUP BY
  [sub].[chardate],
  [sub].[numericmonth]
ORDER BY 
  [sub].[numericmonth] ASC

Here is the Fiddle.

这是小提琴。

#4

The least complicated, most straight-forward way of doing this is by simply wrapping your main query with the pivot in a common table expression, then grouping/aggregating.

最不复杂、最直接的方法是使用公共表表达式中的pivot包装主查询，然后进行分组/聚合。

WITH PivotCTE AS
(
    select * from  mytransactions
    pivot (sum (totalcount) for country in ([Australia], [Austria])) as pvt
)
SELECT
    numericmonth,
    chardate,
    SUM(totalamount) AS totalamount,
    SUM(ISNULL(Australia, 0)) AS Australia,
    SUM(ISNULL(Austria, 0)) Austria
FROM PivotCTE
GROUP BY numericmonth, chardate

The ISNULL is to stop a NULL value from nullifying the sum (because NULL + any value = NULL)

ISNULL的作用是阻止空值使和无效(因为NULL + any值= NULL)