Grouping Sets- Cube and Rollup

Last article we show about grouping sets.

As we know that grouping set is union of different result sets generated by group by clause. Here in group by clause we can use also cube and rollup functions.




Example
create table #t
(id int,
code varchar(10),
department varchar(10),
val int)

insert into #t values (1,'a','d1',10)
insert into #t values (1,'a','d1', 10)
insert into #t values (1,'b','d2',20)
insert into #t values (1,'b','d2',20)
insert into #t values (2,'c','d3',30)
insert into #t values (2,'c','d3',30)
insert into #t values (2,'d','d4',40)
insert into #t values (2,'d','d4',40)


Here we want rollup of code and department
and also we want grouping of ID and union of this two resultset.

select id,code,department,SUM(val) sumofval from #t
group by GROUPING sets (id,rollup ( code,department))

id code department sumofval
NULL a d1 20
NULL a NULL 20
NULL b d2 40
NULL b NULL 40
NULL c d3 60
NULL c NULL 60
NULL d d4 80
NULL d NULL 80
NULL NULL NULL 200
1 NULL NULL 60
2 NULL NULL 140




Here we want cube of code and department
and also we want grouping of ID and union of this two resultset.


select id,code,department,SUM(val) sumofval from #t
group by GROUPING sets (id,cube ( code,department))


id code department sumofval
NULL a d1 20
NULL NULL d1 20
NULL b d2 40
NULL NULL d2 40
NULL c d3 60
NULL NULL d3 60
NULL d d4 80
NULL NULL d4 80
NULL NULL NULL 200
NULL a NULL 20
NULL b NULL 40
NULL c NULL 60
NULL d NULL 80
1 NULL NULL 60
2 NULL NULL 140