13,833,809 members

#### Stats

20.1K views
4 bookmarked
Posted 27 Dec 2012
Licenced CPOL

# Calculate aggregates for dynamic columns using UNPIVOT

, 17 Jan 2013
This tip describes how to calculate an aggregate for dynamically defined columns using UNPIVOT clause.
 ```-- 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'); ```

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

## Share

 Architect Europe
This member does have enough reputation to be able to display their biography and homepage.

 Pro