Recently, I was asked to assist a client with some complex Transact-SQL (T-SQL) tasks they were using to gather some sales data, group the data, summarize it, by salespersons, SKUs, and so forth. In their present mode they were using a lot of "plumbing" to get this all done—including multiple stored procedures, tables and views.
I suggested to the client that there might be an easier way, now that they are SQL Server 2008 or later.
The raw data set returned by a T-SQL query using the WITH ROLLUP command can be seen in the accompanying figure. The advantage of the WITH ROLLUP command is that the query automatically generates aggregates (sums) for the hierarchy of columns in the query's GROUP BY clause.
Here we see the quantity shipped (QtyShipped) and invoice extended amounts (ExtAmt) summarized by ItemClassID within the Salesperson ID (SperID).
Row 5, for example, shows sales for 'Alfred' for products in the 'Hardware' Item Class. Rows 6 and 7 are also for 'Alfred,' but for Item Class IDs 'Service' and 'Software,' respectively. Row 8, however, is the sum of sales values for 'Alfred' for 'ALL Item Classes.' Beyond that, you can see numbers for 'Jeff,' 'John' and 'Mary' with their associated summed values.
This is very helpful, don't you think?
Here is the T-SQL code that generated this data set, and other data sets we will discuss in this article:
/******************************************************************************
Summarizing Data Using ROLLUP and CUBE in SQL Server 2008 and Later
*******************************************************************************
ROLLUP generates a result set that shows aggregates for a hierarchy of values
in the selected columns
CUBE generates a result set that shows aggregates for ALL COMBINATIONS of
values in the selected columns
*******************************************************************************/
set
nocount
on
declare @iCompanyID varchar(3)
declare @iRollupFlag bit
declare @iCubeFlag bit
set @iCompanyID =
'SOA'
set @iRollupFlag = 0
set @iCubeFlag = 1
if @iRollupFlag = 1
begin
-- WITH ROLLUP
select i.CompanyID
,
case
when (grouping(sper.SperID)
= 1 )
then
'ALL Salespersons'
else
isnull(sper.SperID,'None')
end 'SperID'
,
case
when (grouping(ItemClassID)
= 1)
then
'ALL Item Classes'
else
isnull(ItemClassID,'None')
end 'ItemClassID'
,
cast(sum(QtyShipped)
as
int) 'QtyShipped'
,
cast(sum(d.ExtAmt)
as
dec(15,2)) 'ExtAmt'
from dbo.tarInvoiceLineDist ild with (nolock)
join dbo.tarInvoiceDetl d with (nolock)
on ild.InvoiceLineKey = d.InvoiceLineKey
join dbo.timItem i with (nolock)
on d.ItemKey = i.ItemKey
join dbo.timItemClass ic with (nolock)
on i.ItemClassKey = ic.ItemClassKey
join dbo.tarInvoice invc with (nolock)
on d.InvcKey = invc.InvcKey
left
outer
join dbo.tarSalesperson sper with (nolock)
on invc.PrimarySperKey = sper.SperKey
where i.CompanyID = @iCompanyID
group
by i.CompanyID
-- ItemClassID within SperID
, ic.ItemClassID
, sper.SperID
with
rollup
end
if @iCubeFlag = 1
begin
-- WITH CUBE
select i.CompanyID
,
case
when (grouping(sper.SperID)
= 1 )
then
'ALL Salespersons'
else
isnull(sper.SperID,'None')
end 'SperID'
,
case
when (grouping(ItemClassID)
= 1)
then
'ALL Item Classes'
else
isnull(ItemClassID,'None')
end 'ItemClassID'
,
sum(QtyShipped) 'QtyShipped'
,
cast(sum(d.ExtAmt)
as
dec(15,2)) 'ExtAmt'
from dbo.tarInvoiceLineDist ild with (nolock)
join dbo.tarInvoiceDetl d with (nolock)
on ild.InvoiceLineKey = d.InvoiceLineKey
join dbo.timItem i with (nolock)
on d.ItemKey = i.ItemKey
join dbo.timItemClass ic with (nolock)
on i.ItemClassKey = ic.ItemClassKey
join dbo.tarInvoice invc with (nolock)
on d.InvcKey = invc.InvcKey
left
outer
join dbo.tarSalesperson sper with (nolock)
on invc.PrimarySperKey = sper.SperKey
where i.CompanyID = @iCompanyID
group
by i.CompanyID
-- ItemClassID within SperID
, ic.ItemClassID
, sper.SperID
with
cube
end
if @iCubeFlag <> 1 and @iRollupFlag <> 1
begin
print
'No Rollups or Cubes requested.'
set
nocount
off
end
If you wish to change how the sums are arranged in the result set, you may change the order in the GROUP BY clause. For example, using the following GROUP BY clause:
Changes the result set to look like this:
Note that now, the salesperson are on individual lines within Item Class ID, and the sum (as on line 7, for example) is the sum for all salespersons for Item Class ID being grouped.
Using the 'with CUBE' command
The WITH CUBE command is slightly different from the WITH ROLLUP command in that it automatically generates an aggregate for all combinations of values in the columns found in the GROUP BY clause. Here is an example of a CUBE result set on the same data:
Just like the WITH ROLLUP command, changing the order in the GROUP BY clause will change the order in which the aggregated rows appear in the result set of a WITH CUBE command.
Use in reporting, dashboards or to trigger actions
Once returned, data sets gathered using WITH ROLLUP or WITH CUBE may be used just like any other data set. They can be placed into tables (permanent or temporary), or returned to a calling program, such as Crystal Reports or a program capable of managing business alerts or work flows such as KnowlegeSync or TaskCentre. Reporting or actions may be predicated on the values found in any of the columns in the returned data set.
