Cube Operator in SQL Server 2008

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

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


© 2019 DotNetHeaven. All rights reserved.