具有多个列聚合的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)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 44, 8, 'Aug-12', 1368.71)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 52, 9, 'Sep-12', 1161.33)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 50, 10, 'Oct-12', 1099.84)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 38, 11, 'Nov-12', 1078.94)Goinsert into mytransactions(country, totalcount, numericmonth, chardate, totalamount) values('Australia', 63, 12, 'Dec-12', 1668.23)Goinsert 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.96Australia       44         8             Aug-12   1368.71Australia       52         9             Sep-12   1161.33Australia       50         10            Oct-12   1099.84Australia       38         11            Nov-12   1078.94Australia       63         12            Dec-12   1668.23Austria         11         7             Jul-12   257.82Austria          5         8             Aug-12   126.55Austria          7         9             Sep-12   92.11Austria         12         10            Oct-12   103.56Austria         21         11            Nov-12   377.68Austria          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 t

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

GROUP BY t.numericyear, t.numericmonth

ORDER 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

) c

SELECT @Sql=@Sql+'

FROM mytransactions t

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

GROUP 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