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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.