Click here to Skip to main content
13,660,195 members
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads

Tagged as

Stats

18.6K views
42 downloads
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');

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

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







You may also be interested in...

Pro
Pro
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180810.1 | Last Updated 17 Jan 2013
Article Copyright 2012 by Wendelius
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid