CUBE function in SQL Server

mail.yuva's Avatar author of CUBE function in SQL Server
This is an article on CUBE function in SQL Server in SQL Server.
CUBE generates a result set that represents aggregates for all combinations of values in the selected columns. CUBE automatically generates a summation rollup to the top level of a GROUP BY.

Lets us look one Example.



Code:
Create table #TempTable(Items varchar(30),
                                      Model varchar(10),
                                      Price float,
                                      Quantity int)
 
Insert into #TempTable values('Furniture','Chair',100,1)
Insert into #TempTable values('Furniture','Chair',200,1)
Insert into #TempTable values('Furniture','Table',300,2)
Insert into #TempTable values('Electronic Item','Pendrive',450,2)
Insert into #TempTable values('Electronic Item','Pendrive',150,1);
 
Select 
  Case 
        When grouping(Items) = 1 then 'All Items'
        Else Items End 'Item List',
  Case
        When grouping(Model) = 1 then 'All Model'
        Else Model End Models,
  Sum(Price*Quantity) as Total
From #TempTable
Group by Items,
              Model
With Cube
 
Drop table #TempTable
Mittu, suraj2011 likes this