为了账号安全,请及时绑定邮箱和手机立即绑定

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

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

ITMISS 2019-10-18 13:53:03
我有一张桌子: 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
查看完整描述

3 回答

?
慕森卡

TA贡献1806条经验 获得超8个赞

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


静态的


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。


查看完整回答
反对 回复 2019-10-18
?
幕布斯6054654

TA贡献1876条经验 获得超7个赞

我使用您自己的数据透视表作为嵌套查询,并得出以下结果:


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


查看完整回答
反对 回复 2019-10-18
  • 3 回答
  • 0 关注
  • 799 浏览
慕课专栏
更多

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信