SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Two Aggregate Functions in SQL Server Pivot Table Query

SQL Server pivot table provides a powerful t-sql syntax for tsql developers to convert data into pivot structures that users are familier from MS Excel.

It is a common question that t-sql developers ask, to get the output of at least two aggregate functions in the SQL pivot table columns. Of course it is not possible to combine two different values resulting from two aggregate functions only in a single column. The only solution that I can offer for sql programmers is to list values from different sql aggregate functions in different rows under the same pivot table column.





Here is a sql pivot table query which lists two aggregate functions, COUNT() and SUM() in single select query.

Please notice that the below t-sql pivot table query is actually formed of two pivot table queries combined with UNION ALL . It is important to inform users about the aggregate function used which is a short description about the calculations. So in the SELECT list of the two inner subselects, I selected a free text column with empty field name. Actually you can name the description column using a general descriptive text.

SELECT
 *
FROM (
 SELECT
  'Product Count' AS ' ',
  PC.Name ProductCategory,
  P.ProductID
 FROM Production.Product P
 INNER JOIN Production.ProductSubcategory SC
  ON SC.ProductSubcategoryID = P.ProductSubcategoryID
 INNER JOIN Production.ProductCategory PC
  ON PC.ProductCategoryID = SC.ProductCategoryID
) Data
PIVOT (
 COUNT(ProductID)
 FOR ProductCategory
 IN (
  [Bikes],[Components],[Clothing],[Accessories]
 )
) PivotTable

UNION ALL

SELECT
 *
FROM (
 SELECT
  'Make Count' AS ' ',
  PC.Name ProductCategory,
  CASE P.MakeFlag WHEN 1 THEN 1 ELSE 0 END AS MakeFlag
 FROM Production.Product P
 INNER JOIN Production.ProductSubcategory SC
  ON SC.ProductSubcategoryID = P.ProductSubcategoryID
 INNER JOIN Production.ProductCategory PC
  ON PC.ProductCategoryID = SC.ProductCategoryID
) Data
PIVOT (
 SUM(MakeFlag)
 FOR ProductCategory
 IN (
  [Bikes],[Components],[Clothing],[Accessories]
 )
) PivotTable

The output of the above t-sql pivot table query will return two rows as seen in the below screenshot.

SQL Pivot Table query with two aggregate functions



T-SQL Pivot Table in SQL Server 2008 and SQL 2005

For more samples on sql pivot table in SQL Server 2005 and in SQL Server 2008, please refer to the following sql tutorials.

T-SQL Pivot Tables in SQL Server - Pivot Table Tutorial with Examples
T-SQL Dynamic Pivot Table Examples for SQL Server 2008
Create A Sample Bill of Material BOM SQL Query using T-SQL Pivot Command







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems