SQL pivot using 2 aggregate functions?

Question

Is it possible to use pivot with 2 aggregate functions?

Select ItemCode, [ItemName] AS 'Item Name', 
[2019],[2020],[2021],[2022]
FROM
(
SELECT YEAR(T2.[DocDate]) AS 'Years', T0.[ItemCode], T0.[ItemName], 
SUM(T1.[Quantity]) AS 'Quantity', SUM(T1.[LineTotal]) AS 'LineTotals' 
FROM OITM T0  
INNER JOIN INV1 T1 ON T0.[ItemCode] = T1.[ItemCode] 
INNER JOIN OINV T2 ON T1.[DocEntry] = T2.[DocEntry] 
WHERE YEAR(T2.DocDate) >= '2019' AND (T0.[ItemCode] = '825-1-25' OR 
 T0.[ItemCode] = '825-1-25-1S' OR
 T0.[ItemCode] = '825-1-25-5S' OR
 T0.[ItemCode] = '825-1-50' OR
 T0.[ItemCode] = '825-1-50-1S' OR
 T0.[ItemCode] = '825-1-50-5S' OR
 T0.[ItemCode] = '825-10-100' OR
 T0.[ItemCode] = '825-10-100-1S' OR
 T0.[ItemCode] = '825-10-100-5S' )
GROUP BY YEAR(T2.[DocDate]), T0.[ItemCode], T0.[ItemName]

UNION ALL

SELECT YEAR(T2.[DocDate]) AS 'Years', T0.[ItemCode], T0.[ItemName], 
-SUM(T1.[Quantity]) AS 'Quantity', -SUM(T1.[LineTotal]) AS 'LineTotals'
FROM OITM T0  
INNER JOIN RIN1 T1 ON T0.[ItemCode] = T1.[ItemCode] 
INNER JOIN ORIN T2 ON T1.[DocEntry] = T2.[DocEntry] 
WHERE YEAR(T2.DocDate) >= '2019' AND (T0.[ItemCode] = '825-1-25' OR 
 T0.[ItemCode] = '825-1-25-1S' OR
 T0.[ItemCode] = '825-1-25-5S' OR
 T0.[ItemCode] = '825-1-50' OR
 T0.[ItemCode] = '825-1-50-1S' OR
 T0.[ItemCode] = '825-1-50-5S' OR
 T0.[ItemCode] = '825-10-100' OR
 T0.[ItemCode] = '825-10-100-1S' OR
 T0.[ItemCode] = '825-10-100-5S' )
GROUP BY YEAR(T2.[DocDate]), T0.[ItemCode], T0.[ItemName] 
) AS SourceTable
PIVOT 
(
SUM(LineTotals) & SUM(Quantity)
FOR Years 
IN ( [2019], [2020], [2021], [2022] )
) 
AS PivotTable

Answer 1

Hi @Jonathan Brotto
It is a common question 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.
Here are some solutions and samples that I can offer to you.

--test data  
CREATE TABLE #TEST(ItemCode INT,[Year] INT,Quantity INT,LineTotal INT)  
INSERT INTO #TEST VALUES(101,2019,111,1111),(102,2019,222,2222),(103,2020,333,1111),(101,2020,111,2222),(102,2021,222,1111),(103,2021,222,3333)  
SELECT * FROM #TEST  
--sample 1  
SELECT  * FROM ( SELECT 'Quantity SUM' AS ' ',ItemCode,Quantity,[Year] FROM #TEST ) S  
         PIVOT ( SUM(Quantity) FOR [Year] IN ( [2019],[2020],[2021] ) ) P  
UNION ALL  
SELECT  * FROM ( SELECT 'LineTotal SUM' AS ' ',ItemCode,LineTotal,[Year] FROM #TEST ) S  
         PIVOT ( SUM(LineTotal) FOR [Year] IN ( [2019],[2020],[2021] ) ) P  

And these two :

--sample 2  
SELECT * From   
(SELECT A.ItemCode,B.*  
 From  #TEST A  
 CROSS APPLY ( VALUES (CAST([Year] AS VARCHAR)+' Quantity SUM', Quantity ) ,(CAST([Year] AS VARCHAR)+' LineTotal SUM',LineTotal) ) B (Item,Value)) A  
 PIVOT (SUM([Value]) For [Item] in ([2019 Quantity SUM], [2019 LineTotal SUM],[2020 Quantity SUM], [2020 LineTotal SUM],[2021 Quantity SUM], [2021 LineTotal SUM]) ) p  
--sample 3  
SELECT ItemCode,  
       SUM(CASE WHEN [Year]=2019 THEN Quantity END) AS [2019 Quantity SUM],  
	   SUM(CASE WHEN [Year]=2019 THEN LineTotal END) AS [2019 LineTotal SUM],  
	   SUM(CASE WHEN [Year]=2020 THEN Quantity END) AS [2020 Quantity SUM],  
	   SUM(CASE WHEN [Year]=2020 THEN LineTotal END) AS [2020 LineTotal SUM],  
	   SUM(CASE WHEN [Year]=2021 THEN Quantity END) AS [2021 Quantity SUM],  
	   SUM(CASE WHEN [Year]=2021 THEN LineTotal END) AS [2021 LineTotal SUM]  
FROM #TEST  
GROUP BY ItemCode  

Best regards,
LiHong

Answer 2

PIVOT is a useless operator. I have not even bothered to learn it.

Here is an example of a pivot statement without the PIVOT operator:

SELECT P.ProductName, 
       SUM(CASE C.Country WHEN 'Brazil'  THEN OD.Amount END) AS Brazil,
       SUM(CASE C.Country WHEN 'France'  THEN OD.Amount END) AS France,
       SUM(CASE C.Country WHEN 'Germany' THEN OD.Amount END) AS Germany,
       SUM(CASE C.Country WHEN 'UK'      THEN OD.Amount END) AS UK,
       SUM(CASE C.Country WHEN 'USA'     THEN OD.Amount END) AS USA
FROM   dbo.Orders O
JOIN   dbo.[Order Details] OD ON O.OrderID = OD.OrderID
JOIN   dbo.Customers C ON C.CustomerID = O.CustomerID
JOIN   dbo.Products P ON P.ProductID = OD.ProductID
WHERE  C.Country IN ('Brazil', 'France', 'Germany', 'USA', 'UK')
GROUP  BY P.ProductName
ORDER  BY P.ProductName

The interesting part here is that if you also want averages, grand totals and whatnots, you simply add them, because there is no PIVOT straight jacket.

The example is taken from my article on dynamic SQL, which includes a section on dynamic pivot. Which starts off with a discussion on static pivot, since if you don't master static pivot, you cannot do dynamic pivot.
https://www.sommarskog.se/dynamic_sql.html#pivot.

Answer 3

The short answer is no. You need to use case based pivot, e.g.
sum(case when year(date) = 2019 then Quantity end) as 2019_Totals, etc.

Answer 4

Take a look at this WiKi article https://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

For your case you don't need it to be dynamic, use a static query but with the case based.