Grouping sets in SQL Server 2008

Its a new feature in SQL Server 2008. It generate a result of union all of group by result of different set of columns.
We can also use cube and rollup function with grouping sets.



For Example

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

insert into #t values (1,'a',10)
insert into #t values (1,'b',10)
insert into #t values (2,'a',20)
insert into #t values (2,'b',20)


select Id, SUM(val)sumofval from #t
group by id

Id sumofval
1 20
2 40

select code, SUM(val) sumofval from #t
group by code

code sumofval
a 30
b 30

Union ALL of this two result can be obtained by single Query.

Here it counts id, code as different set. Sets are divided by comma.

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

id code sumofval
NULL a 30
NULL b 30
1 NULL 20
2 NULL 40

Here id, code are in same set so it has same effect as group by.

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


id code sumofval
1 a 10
2 a 20
1 b 10
2 b 20


On Next article we will look on cube and rollup using grouping sets.