添加来自PIVOT的其他列的SUM列

Question

我有这个枢轴工作得很好，但我意识到，对于我正在构建的立方体，将总数作为最后一列会很方便。

我脑子里有个障碍，想不出最后的总数。

SELECT [Date],
[Type],
Typology,
Instrument,
Market,
Curve,
ISNULL([6M], 0) AS [6M],
ISNULL([1Y], 0) AS [1Y],
ISNULL([2Y], 0) AS [2Y],
ISNULL([3Y], 0) AS [3Y],
ISNULL([4Y], 0) AS [4Y],
ISNULL([5Y], 0) AS [5Y],
ISNULL([6Y], 0) AS [6Y],
ISNULL([7Y], 0) AS [7Y],
ISNULL([8Y], 0) AS [8Y],
ISNULL([9Y], 0) AS [9Y],
ISNULL([10Y], 0) AS [10Y],
ISNULL([11Y], 0) AS [11Y],
ISNULL([12Y], 0) AS [12Y],
ISNULL([15Y], 0) AS [15Y],
ISNULL([20Y], 0) AS [20Y],
ISNULL([25Y], 0) AS [25Y],
ISNULL([30Y], 0) AS [30Y],
ISNULL([40Y], 0) AS [40Y]
FROM (SELECT [Date],
Typology,
Instrument,
Market,
Type,
Curve,
Pillar,
Amount
FROM tblActivePivotSensiBondDaily
WHERE CONVERT(varchar(8), [Date], 112) = '20220525'
AND type = 'CS01') source
PIVOT (SUM(Amount)
FOR Pillar IN ([6M], [1Y], [2Y], [3Y], [4Y], [5Y], [6Y], [7Y], [8Y], [9Y], [10Y], [11Y], [12Y], [15Y], [20Y], [25Y], [30Y], [40Y])) pillars
ORDER BY Instrument;

Answer 1

正如我提到的，我个人建议切换到条件聚合。那么你可以直接用SUM作为你的总数:

SELECT [Date],
Typology,
Instrument,
Market,
Type,
Curve,
SUM(CASE Pillar WHEN '6M' THEN Amount END) AS [6M],
SUM(CASE Pillar WHEN '1Y' THEN Amount END) AS [1Y],
...
SUM(CASE Pillar WHEN '4Y' THEN Amount END) AS [40Y],
SUM(Amount) AS Total
FROM dbo.tblActivePivotSensiBondDaily
WHERE [date] = '20220525' -- I assume the column [date] is the date data type.
GROUP BY [Date],
Typology,
Instrument,
Market,
Type,
Curve
ORDER BY Instrument;

Answer 2

可以使用窗口函数来实现逐行求和。我假设行中的唯一标识符是Instrument，因此窗口函数将为

sum()over(partition by Instrument) as 'Total_Amount'

结果查询:

SELECT [Date],
[Type],
Typology,
Instrument,
Market,
Curve,
ISNULL([6M], 0) AS [6M],
ISNULL([1Y], 0) AS [1Y],
ISNULL([2Y], 0) AS [2Y],
ISNULL([3Y], 0) AS [3Y],
ISNULL([4Y], 0) AS [4Y],
ISNULL([5Y], 0) AS [5Y],
ISNULL([6Y], 0) AS [6Y],
ISNULL([7Y], 0) AS [7Y],
ISNULL([8Y], 0) AS [8Y],
ISNULL([9Y], 0) AS [9Y],
ISNULL([10Y], 0) AS [10Y],
ISNULL([11Y], 0) AS [11Y],
ISNULL([12Y], 0) AS [12Y],
ISNULL([15Y], 0) AS [15Y],
ISNULL([20Y], 0) AS [20Y],
ISNULL([25Y], 0) AS [25Y],
ISNULL([30Y], 0) AS [30Y],
ISNULL([40Y], 0) AS [40Y],
Total_Amount
FROM (SELECT [Date],
Typology,
Instrument,
Market,
Type,
Curve,
Pillar,
Amount,
sum(Amount)over(Partition by Instrument) as 'Total_Amount'
FROM tblActivePivotSensiBondDaily
WHERE CONVERT(varchar(8), [Date], 112) = '20220525'
AND type = 'CS01') source
PIVOT (SUM(Amount)
FOR Pillar IN ([6M], [1Y], [2Y], [3Y], [4Y], [5Y], [6Y], [7Y], [8Y], [9Y], [10Y], [11Y], [12Y], [15Y], [20Y], [25Y], [30Y], [40Y])) pillars
ORDER BY Instrument;