Cube Operator in SQL Server 2008

In this article I describe about cube operator in SQL Server.
  • 2374

Introduction

In this article I describe about cube operator in SQL Server 2008.

Cube Operator

Cube operator is used with Group By clause to return every possible combination of group or subgroup in the result. It presents the data in aggregate form.

First of all we create a table named emp

Creation of table:

create table Stocks

    (

    Apparel varchar(15),

    Store varchar(15),

    Number int

    )

Insertion of data:

  insert into Stocks

   select 'Shirt', 'Bindals' ,25 union all

   select 'Trousers','Bindals', 30 union all

   select 'Jeans', 'Labhjis' ,15 union all

   select 'Shirt', 'Labhjis', 25 union all

   select 'Jeans', 'Labhjis', 34

A table emp has the following records:

select * from Stocks

Output:

table-in-sql.jpg

Using Cube Operator:

Now if we want to quick look at various apparels stock, type following code to see all possible combinations of Apparel and Store:

select Apparel,Store, SUM(Number) AS Quantity

FROM Stocks

GROUP BY Apparel, Store WITH CUBE

Output:

grouping-throughcubeOperator-in-sql.jpg


© 2020 DotNetHeaven. All rights reserved.