sql - SQL Server Pivot 总计?
问题描述
我可以从一张桌子上动态地与我的派对分享一个故事,下面的代码工作正常,下面没有问题。
SELECT @PartiesNames += QUOTENAME(LTRIM(RTRIM([Party]))) + ','
FROM table1 WHERE [Party] <> ''
GROUP BY [Party]
SET @PartiesNames = LEFT(@PartiesNames, LEN(@PartiesNames) - 1)
SET @SQL =
'
SELECT *
FROM (SELECT
table1.[Party]
,table1.[Accounts]
,[Amount] = FORMAT(SUM(table1.[Amount]),''N0'')
FROM table1
GROUP BY table1.[Party]
,table1.[Accounts]
) AS BaseData
PIVOT(
MAX(BaseData.[Amount])
FOR BaseData.[Party]
IN(' + @PartiesNames + '
)
) AS PivotTable
'
EXEC( @SQL)
我得到了一个正常的数据透视表,但我的问题是,如何调整此代码以获取新的行或/和列的总数?我一直在关注这篇关于如何做到这一点的文章,但很难理解:https ://www.codeproject.com/Articles/232181/SQL-Pivot-with-Grand-Total-Column-and-Row
我对如何解决这个问题的新解决方案持开放态度。我相信这很难解决的一个原因是因为在线资源没有得到很好的解释,而且它确实很快变得棘手,无论如何我感谢支持和建议。
解决方案
如果对辅助函数感兴趣。
这个表值函数返回Count,Sum,Min,Max,Avg and STDev
几乎任何行的。
您所要做的就是CROSS APPLY
在PIVOT
.
我应该补充一点,您必须“排除”您不希望在聚合中出现的任何数字列
例子
Declare @T1 Table ([Branch] varchar(50),[Item] varchar(50),[Sales] int)
Insert Into @T1 Values
('23','Apples',25)
,('23','Coffee',56)
,('23','Bread',22)
,('14','Apples',56)
,('14','Coffee',65)
,('14','Bread',null)
Select Pvt.*
,Ttl.*
From @T1
Pivot ( max(Sales) for Item in ([Apples],[Coffee],[Bread]) ) Pvt
Cross Apply [dbo].[tvf-Stat-Row-Agg]('Branch,OtherColumsToExclude',(Select Pvt.* for XML Raw)) Ttl
退货
/---- From Pivot ----\ /---- From CROSS APPLY ----\
Branch Apples Coffee Bread RetCnt RetSum RetMin RetMax RetAvg RetStd
14 56 65 NULL 2 121 56 65 60.5 6.3639
23 25 56 22 3 103 22 56 34.33 18.8237
TVF 如果有兴趣
CREATE FUNCTION [dbo].[tvf-Stat-Row-Agg](@Exclude varchar(500),@XML xml)
Returns Table
As
Return (
Select RetCnt = count(value)
,RetSum = Sum(Value)
,RetMin = Min(Value)
,RetMax = Max(Value)
,RetAvg = Avg(Value)
,RetStd = Stdev(Value)
From (
Select Item = xAttr.value('local-name(.)','varchar(100)')
,Value = try_convert(float,xAttr.value('.','varchar(max)'))
From @XML.nodes('//@*') x(xAttr)
) S
Where charindex(','+S.Item+',',','+@Exclude+',')=0
);
推荐阅读
- quarkus - 复制资源失败:java.nio.file.NoSuchFileException
- excel - VBA:复制公式,而其他工作表中的单元格不为空
- java - 使用并发哈希映射时结果不一致
- javascript - 复选框的标签未按应有的方式显示
- c - 我的 && 函数在程序上没用。有人可以帮助我如何使用它
- c# - 在 Selenium C# 中显示为启用的禁用元素
- ios - 无法更改 iOS 13 中大标题的导航栏色调颜色
- ios - UserInterfaceStyle Light 不适用于发布版本
- pyspark - Pyspark 与 AWS Glue 将 1-N 关系加入 JSON 数组
- amazon-web-services - 将 CSV 文件导入 Amazon Personalize 时出错