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.
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