Calculate aggregates for dynamic columns using UNPIVOT

-- Test table
CREATE TABLE TestData (
   RowId int,
   Col1  int,
   Col2  int,
   Col3  int
);

-- Populate with data
INSERT INTO TestData VALUES (1, 1, 2, 3);
INSERT INTO TestData VALUES (2, 4, 5, 6);
INSERT INTO TestData VALUES (3, 7, 8, 9);
INSERT INTO TestData VALUES (4, 10, 11, 12);



-- Intermediate results, case 1
SELECT td2.RowId, td2.ColHeader, td2.ColValues
FROM (SELECT RowId, Col1, Col2 
      FROM TestData) td
UNPIVOT (ColValues FOR ColHeader IN (Col1, Col2)) AS td2;

-- Intermediate results, case 2
SELECT td2.RowId, td2.ColHeader, td2.ColValues
FROM (SELECT RowId, Col2, Col3 
      FROM TestData) td
UNPIVOT (ColValues FOR ColHeader IN (Col2, Col3)) AS td2;



-- Overall result, case 1
SELECT SUM(iv.ColValues) AS TotalSum
FROM (SELECT td2.RowId, td2.ColHeader, td2.ColValues
      FROM (SELECT RowId, Col1, Col2 
            FROM TestData) td
      UNPIVOT (ColValues FOR ColHeader IN (Col1, Col2)) AS td2
	  ) iv;

-- Overall result, case 2
SELECT SUM(iv.ColValues) AS TotalSum
FROM (SELECT td2.RowId, td2.ColHeader, td2.ColValues
      FROM (SELECT RowId, Col2, Col3 
            FROM TestData) td
      UNPIVOT (ColValues FOR ColHeader IN (Col2, Col3)) AS td2
	  ) iv;



-- Dynamic definition of columns, case 1
DECLARE @columns VARCHAR(1000);
SET @columns = 'Col1, Col2';

EXECUTE ('SELECT SUM(iv.ColValues) AS TotalSum
FROM (SELECT td2.RowId, td2.ColHeader, td2.ColValues
      FROM (SELECT RowId, ' + @columns + ' 
            FROM TestData) td
      UNPIVOT (ColValues FOR ColHeader IN (' + @columns + ')) AS td2
	  ) iv');

-- Dynamic definition of columns, case 2
DECLARE @columns VARCHAR(1000);
SET @columns = 'Col2, Col3';

EXECUTE ('SELECT SUM(iv.ColValues) AS TotalSum
FROM (SELECT td2.RowId, td2.ColHeader, td2.ColValues
      FROM (SELECT RowId, ' + @columns + ' 
            FROM TestData) td
      UNPIVOT (ColValues FOR ColHeader IN (' + @columns + ')) AS td2
	  ) iv');