CUBE function in SQL Server

Discussion in 'SQL Server' started by mail.yuva, Feb 9, 2011.

  1. mail.yuva

    mail.yuva New Member

    Joined:
    Jan 10, 2011
    Messages:
    44
    Likes Received:
    9
    Trophy Points:
    0
    Occupation:
    Software Developer
    Location:
    Coimbatore
    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
    
     
    suraj2011 and Mittu like this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice