This is note that I test various Data Aggregation functions in SQL Server.
- GROUPING SET
In the simple GROUP, it returns 27 rows. ROLLUP provides some combination of the sum, it return 37 rows. CUBE provides all different combination of SUM, it returns 126 rows.
GROUPING SET gives ability to choice what returns in the CUBE we want to see.
GROUPING provides additional column to indicate whether that particular column is summary data or not.
GROUPING_ID, similar to GROUPING but give us the ID to identify what kind of summary for that row.
PIVOT functions convert row data in to column.
UNPIVOT functions convert column data back to row. However, if there are 2 row data before, it can not distinguish but only convert one row data back.
Above table will become Pivot as below
If we UNPIVOT it, it will change to below
SQL server can not distinguish there were 2 rows of Item 3 in 2014, so it combine both into one row.
Create the test View
Below is the test View. I didn’t come up with the view myself, it is coming from Suherman, STP