Grouping sets, CUBE, and ROLLUP in SQL SERVER

Grouping sets, CUBE, and ROLLUP in SQL SERVER
Preface
In the full text, we will use a CTE statement as the basic data:

With testas (select N 'leewhoeeunisersity 'as name, n' database' as category, 30 as totalcount union all select N 'leewhoeeunisersity ','.. NET ', 20 union all select N 'depop', N '. NET ', 40 union all select N 'depop', N 'web Design', 30) select * from test

Result set:

Name category totalcount
LeeWhoeeUnisersity database 30
LeeWhoeeUnisersity. NET 20
DePaul. NET 40
DePaul WEB design 30

Grouping sets use the group by clause of grouping sets to generate a result set equivalent to the union all generated BY multiple simple group by clauses.

SELECT customer, year, SUM (sales) from tgroup by grouping sets (customer), (year) and SELECT customer, NULL as year, SUM (sales) from t group by mermerunion allselect null as customer, year, SUM (sales) from t group by year is equivalent.

View instances:

With testas (select N 'leewhoeeunisersity 'as name, n' database' as category, 30 as totalcount union all select N 'leewhoeeunisersity ','.. NET ', 20 union all select N 'depop', N '. NET ', 40 union all select N 'depop', N 'web Design', 30) select name, category, sum (totalcount) as [sum] from testgroup by grouping sets (name), (category ))

Result:

Name	Category	Sum
	. NET	60
	WEB design	30
	Database	30
DePaul		70
LeeWhoeeUnisersity		50

ROLLUP

Group by rollup (C1, C2 ,..., Cn-1, Cn) or group by C1, C2 ,..., Cn-1, Cn with rollup and group by grouping sets (C1, C2 ,..., Cn-1, Cn), (C1, C2,..., Cn-1)..., (C1, C2), (C1), () is equivalent. Note that with rollup is written in earlier versions. group by rollup can only run in versions WITH compatibility greater than 100.

Instance:

With testas (select N 'leewhoeeunisersity 'as name, n' database' as category, 30 as totalcount union all select N 'leewhoeeunisersity ','.. NET ', 20 union all select N 'depop', N '. NET ', 40 union all select N 'depoper', N 'web Design', 30) select name, category, sum (totalcount) as [sum] from testgroup by rollup (name, category)

Equivalent

With testas (select N 'leewhoeeunisersity 'as name, n' database' as category, 30 as totalcount union all select N 'leewhoeeunisersity ','.. NET ', 20 union all select N 'depop', N '. NET ', 40 union all select N 'depop', N 'web Design', 30) select name, category, sum (totalcount) as [sum] from testgroup by grouping sets (name, category), (name ),())

Result:

Name	Category	Sum
DePaul	. NET	40
DePaul	WEB design	30
DePaul		70
LeeWhoeeUnisersity	. NET	20
LeeWhoeeUnisersity	Database	30
LeeWhoeeUnisersity		50
		120

The result set generated by ROLLUP displays the aggregation of a certain hierarchy of values in the selected column, such as "DePaul 70, LeeWhoeeUniversity 50 ". (..., () The last pair of parentheses () indicates that all are summarized, that is, 120 in the result.
CUBE

GROUP BY CUBE (C1, C2, C3)
Equivalent
GROUP BY GROUPING SETS ( (C1, C2, C3)    ,(C1, C2)    ,(C1, C3)    ,(C2, C3)    ,(C1)    ,(C2)    ,(C3)    ,() )

Perform CUBE testing:

With testas (select N 'leewhoeeunisersity 'as name, n' database' as category, 30 as totalcount union all select N 'leewhoeeunisersity ','.. NET ', 20 union all select N 'depop', N '. NET ', 40 union all select N 'depop', N 'web Design', 30) select case when grouping (name) = 1 then 'allnames' else name end as name, case when grouping (category) = 1 then 'allcategories 'else category end as category, sum (totalcount) as sumfrom testgroup by cube (name, category)

Equivalent

With testas (select N 'leewhoeeunisersity 'as name, n' database' as category, 30 as totalcount union all select N 'leewhoeeunisersity ','.. NET ', 20 union all select N 'depop', N '. NET ', 40 union all select N 'depop', N 'web Design', 30) select case when grouping (name) = 1 then 'allnames' else name end as name, case when grouping (category) = 1 then 'allcategories 'else category end as category, sum (totalcount) as sumfrom testgroup by grouping sets (name, category), (name ), (category ),())

Result:
Name category sum
LeeWhoeeUnisersity. NET 20
DePaul. NET 40
Allnames. NET 60
DePaul WEB design 30
Allnames WEB design 30
LeeWhoeeUnisersity database 30
Allnames database 30
Allnames allcategories 120
LeeWhoeeUnisersity allcategories 50
DePaul allcategories 70

The result set generated by CUBE displays the aggregation of all the combinations of the values in the selected column.

Aggregate Function GROUPING: When the rows in the result set are generated by grouping sets, CUBE, or ROLLUP, the value is 1 or 0.

If GROUPING is not determined by case when, all allnames and allcategories above will be replaced by NULL.