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.


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);
        When grouping(Items) = 1 then 'All Items'
        Else Items End 'Item List',
        When grouping(Model) = 1 then 'All Model'
        Else Model End Models,
  Sum(Price*Quantity) as Total
From #TempTable
Group by Items,
With Cube
Drop table #TempTable

All times are GMT +5.5. The time now is 14:49.