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

Question

我有一张桌子：

 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

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

Answer 1

我添加了动态查询/解决方案。静态的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 tGROUP BY t.chardate;注意：1）ORDER BY t.chardate将不起作用，因为chardate列中char的值为s。2）我的建议是分为chardate两列numericmonth和numericyear。在后一种情况下，您可以使用以下解决方案：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 tGROUP BY t.numericyear, t.numericmonthORDER BY BY t.numericyear, t.numericmonth;动态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) cSELECT @Sql=@Sql+'FROM mytransactions tGROUP BY t.chardate;'PRINT @Sql;EXEC(@Sql);结果：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 tGROUP BY t.chardate;注意：REPLACEfrom 的功能SELECT @Sql=@Sql+CHAR(13)+ ... REPLACE(c.name,'''',''''''))用于防止SQL injections。

Answer 2

我使用您自己的数据透视表作为嵌套查询，并得出以下结果：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