Add a SUM column of other columns from PIVOT-CodePudding

I have this pivot working well but I realised for the Cube I'm building it would be convenient to have the total as the last column.

I have a mind block on getting the total on the end.

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;

CodePudding user response：

As I mentioned, I would personally suggest switching to conditional aggregation. Then you can just SUM for your total:

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;

CodePudding user response：

you can use window function to achieve to get row wise sum. I assumed that the unique identifier in your rows is Instrument so the window function will be

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

Resulted query:

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;