從PIVOT添加其他列的SUM列

我的這個支點運作良好，但我意識到對于我正在構建的多維資料集，將總數作為最后一列會很方便。

我對最終獲得總數感到困惑。

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;

uj5u.com熱心網友回復：

正如我所提到的，我個人建議切換到條件聚合。然后你可以只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;

uj5u.com熱心網友回復：

您可以使用視窗函式來實作逐行求和。我假設您的行中的唯一識別符號是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;

標籤：sql sql服务器 tsql 和 枢

上一篇：PRECISIONLENGTH`和有什么不一樣？

下一篇：獲取導致執行觸發器的插入行