Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/articles/sql-server/)
-   -   CUBE function in SQL Server (http://www.go4expert.com/articles/cube-function-sql-server-t24911/)

mail.yuva 9Feb2011 12:17

CUBE function 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



All times are GMT +5.5. The time now is 06:19.