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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.