SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2017
download SQL Server 2016
download SQL Server 2014

SQL Server T-SQL OLAP WITH CUBE Operator Example

T-SQL developers and Business Intelligence professionals probably know Microsoft SQL Server 2005 has new SQL OLAP commands that empowers the OLAP reporting tools that developers have in T-SQL.
If you want to learn more about SQL/OLAP enhancements in SQL Server 2005, please refer to sql tutorial T-SQL WITH CUBE Example in SQL Server.

I want to give an other SQL Server T-SQL OLAP With Cube operator example in this sql tutorial.

SQL/OLAP WITH CUBE Operator Example

Let's create a real life scenario of course with dummy numbers for T-SQL With Cube example in this sql tutorial.
Assume that you have a database table where you store number of tourists visiting your hotels as a travel agency.
You keep the date and transportation medium as well as the destination point in your SQL Server database table.
And your final target is to build sql queries which will help you analyze sql data.

First create sample sql database table for this WITH CUBE SQL example.

Create Table TouristVisits
 [Arrival Date] date,
 [Transportation] varchar(20),
 [Destination] varchar(20),
 [Count] int

Insert Into TouristVisits Values
('20100615', 'Plane', 'Antalya', 1000),
('20100625', 'Plane', 'Cesme', 100),
('20100628', 'Ship', 'Bodrum', 2000),
('20100712', 'Car', 'Cesme', 2000),
('20100712', 'Plane', 'Cesme', 5000),
('20100716', 'Ship', 'Antalya', 500),
('20100722', 'Plane', 'Cesme', 10000),
('20100727', 'Car', 'Bodrum', 3000)

And the t-sql With Cube sql query which can be used for a sample sql data analyze is as follows:

 Transportation, Destination, SUM([Count]) Total
FROM TouristVisits
GROUP BY Transportation, Destination

When sql programmers execure the above SQL Server T-SQL OLAP WITH CUBE query the resultset will be as seen in the below screenshot.


T-SQL developers can alter the sql WITH CUBE query as follows to get more information from the SQL OLAP query using SQL Server WITH CUBE operator. The changed t-sql SELECT statement will give results in time dimension as well.

 DATENAME(MONTH, [Arrival Date]),
 Transportation, Destination, SUM([Count]) Total
FROM TouristVisits
GROUP BY DATENAME(MONTH, [Arrival Date]), Transportation, Destination


Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems