Unpivot and summarize a table

I will show two techniques to unpivot and summarize a pivoted


P1.png

Considering the table below

Declare @table table (Dept varchar(30), Month1 decimal(16,4), Month2 decimal(16,4), Month3 decimal(16,4), Month4 decimal(16,4))
Insert @table (Dept, Month1, Month2, Month3,Month4) values
 ('Dog'		, 80.06		,80.0709	,80.0709	,80.8153)
,('Dog'		, 61.64		,null		,81.8364	,79.1241)
,('Dog'		, 48.34		,79.0159	,null		,null)
,('Dog'		, null		,87.5137	,83.7288	,75.454)
,('Horses'	, 85.13		,89.36		,89.36		,89.36)
,('Horses'	, 82.21		,91.2732	,91.334		,null)
,('Horses'	, 106.19	,NULL		,80.3801	,82.2787)
,('Horses'	, 94.44		,75.0055	,75.4232	,75.4335)
,('Horses'	, 119.77	,87.6069	,88.0649	,null)

unpivot

The first technique is using the operation unpivot, in this technique, you will write more code

SELECT dept 
       ,Sum(sales) Sum 
       ,Avg(sales) Agv 
FROM   (SELECT dept 
               ,month 
               ,sales 
        FROM   (SELECT dept 
                       ,month1 
                       ,month2 
                       ,month3 
                       ,month4 
                FROM   @table) p 
               UNPIVOT (sales 
                       FOR month IN (month1 
                                     ,month2 
                                     ,month3 
                                     ,month4) ) U 
        ) T 
GROUP  BY dept 

Cross apply

The other is using the Cross apply

SELECT A.dept, 
       MonthTotal = Sum(B.value), 
       MonthAvg = Avg(B.value) 
FROM   @table A 
       CROSS apply ( VALUES (month1), 
                            (month2), 
                            (month3), 
                            (month4) ) B(value) 
GROUP  BY A.dept 

Conclusion

Both techniques will work but when you are using the Cross-apply the query execution a more efficient query.
P2.png
P3.png

Advertisements