Calculate aggregates for dynamic columns using UNPIVOT

• Download DynamicColumnUnpivot.zip - 638 B

Introduction 

Sometimes an aggregate calculation such as SUM or AVG is needed on dynamically defined columns. For example, if you have several columns in a table, you may need to calculate a SUM for columns 1 and 2 or in another situation for columns 2 and 3.

This can be done in several ways, for example using a cursor, fetching all the values and aggregating the needed result. Another way could be to define a condition for each column if the column is included in the calculation or not. Based on that information, do the aggregation and so on.

This tip demonstrates how this task can be accomplished by using UNPIVOT with dynamically defined set of columns.  

Test data 

First we need some test data. For that let's create a table.

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

And populate it with some 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 

To understand the solution, let's have a look how the data from separate columns is transferred to rows.

For the first situation, values from columns Col1 and Col2 are needed in a single result column in order to calculate a sum. For this, the following statement can be used.

SELECT td2.RowId, td2.ColHeader, td2.ColValues
FROM (SELECT RowId, Col1, Col2 
      FROM TestData) td
UNPIVOT (ColValues FOR ColHeader IN (Col1, Col2)) AS td2; 

The query above selects the values from both Col1 and Col2 and places them in the result column called ColValues. The name of the source column is placed in ColHeader. The RowId is included to show from which row the data is coming from. 

So the result looks like the following

RowId   ColHeader   ColValues
-----   --------    ---------
1       Col1        1
1       Col2        2
2       Col1        4
2       Col2        5
3       Col1        7
3       Col2        8
4       Col1        10
4       Col2        11 

The same query to get values from Col2 and Col3 would look like 

SELECT td2.RowId, td2.ColHeader, td2.ColValues
FROM (SELECT RowId, Col2, Col3 
      FROM TestData) td
UNPIVOT (ColValues FOR ColHeader IN (Col2, Col3)) AS td2; 

And the result 

RowId   ColHeader   ColValues
-----   --------    ---------
1       Col2        2
1       Col3        3
2       Col2        5
2       Col3        6
3       Col2        8
3       Col3        9
4       Col2        11
4       Col3        12 

To calculate the aggregate 

Now, when we have the intermediate results, let's do the aggregate calculation. In order to keep this in a single query, the queries above are used as inline view in the SQL statement 

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;  

So the previous example query is just surrounded with an outer SELECT statement and the aggregation is done in the outermost statement. The result from the query above is

TotalSum
--------
48 

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;

TotalSum
--------
56 

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'); 

TotalSum
--------
48 

...
SET @columns = 'Col2, Col3';
... 

TotalSum
--------
56