Full resolution of data Rollup for SQL Server database (with ROLLUP) _mssql

Existing table A, which reads as follows:

Number of encoded warehouses 6 b 7 a 8 B 9

Now you want to query this format by encoding:

6 b 7 Summary subtotal: 8 B 9 Summary subtotal: 17

Q: How do I achieve this?

At first glance, it seems easy to use group by as if it can be achieved? But after careful study, you will feel that group by is powerless, the total lack of something, no way to go. So what exactly is to be done? Don't worry, SQL Server has already done it for us, below, follow me.

First, let's take a look at some of the words:

The ROLLUP operator is useful when generating reports that contain subtotals and totals. The ROLLUP operator produces a result set similar to the result set generated by the cube operator.

The cube operator produces a result set that is a multidimensional dataset. A cube is an extension of the fact data, which is the data that records individual events. The extension is based on the columns that the user intends to analyze. These columns are called dimensions. A cube is a result set that contains a crosstab table of all possible combinations of each dimension.

The CUBE operator is specified in the GROUP by clause of the SeleCT statement. The select list of the statement should contain dimension columns and aggregate function expressions. GROUP by should specify dimension columns and keywords with CUBE. The result set will contain all possible combinations of the values in the Dimension column and the aggregate values in the underlying rows that match the combination of these dimension values.

The difference between CUBE and ROLLUP is:

Cube produces a result set that shows aggregates for all combinations of values in the selected column.

The result set generated by ROLLUP shows the aggregation of a hierarchy of values in the selected column.

After reading this passage, what do you realize? If not, then ... Hey, your savvy is not enough yo, from the "Three flower 棸 top" is still early:). Next we'll look at a paragraph (note that the answer is immediately revealed):

SELECT code, Warehouse, Sum (quantity) as quantity from A GROUP by encoding, warehouse with ROLLUP

--The key is the back with ROLLUP

Of course, you can also use the with CUBE, but the result will be a little bit the same

Perhaps after reading the above paragraph you still feel "foggy", not touch the mind. I don't know, it doesn't matter, do it yourself.

First: Build a table mentioned above, enter a few rows of data;

Next: Open your SQL Server Query Analyzer, connect to the server that contains the table you built above, and select the database that contains the table;

Then: Copy the above SQL statement, paste to Query Analyzer, press F5, how? See what's coming down here? Is it the same as the one below me?

Code warehouse Quantity 6 B 7 NULL 8 02 B 9 null null 30

If you are using the WITH cube, there will be two more after the result set (if you are just entering a few rows of data in the example):

Null A null B 16

Hey! Strange, how can there be so many "NULL" values in the results? Ah, don't worry, these lines are just the summary data lines we want, it's not hard to see:

The sum of NULL 13 is a summary of the number of all warehouses encoded as 01, and NULL 17 is a summary of the number of all warehouses encoded as 02;

Null NULL 30 is a summary of the number of all rows.

How is it? Has the answer come out? Is it simple? Of course, there is a bit of a drawback, that is, there are many "NULL" existence. How do you get rid of these meaningless null? Let's do the optimizations below.

1. Replace null value with grouping

SELECT Case When (GROUPING (encoded) = 1) THEN ' all ' ELSE ISNULL (encoding, ' UNKNOWN ') end as encoding, case when (GROUPING (warehouse) = 1) THEN ' all ELSE ISNULL (Warehouse, ' UNKNOWN ') end as Warehouse, SUM (quantity) as quantity from A GROUP by encoding, warehouse with ROLLUP

--appropriate use of case functions

As a result, I'm not going to write any of the above "NULL" values into the "all" string.

2, use the program to do further optimization

Usually for the sake of display, we have to do some optimization on the results of the above SQL statements, and give a description of the natural language below:

While (the last record not reached) {The IF encoding value is not all and the warehouse value is all {] replaces the encoded value with "Subtotal:" and replaces the warehouse value with "". 　　Mark the color of this line as Gray; 　　　　　ELSE the encoding value is the all warehouse value and all {replaces the encoded value with "total:" and replaces the warehouse value with ""). 　　Mark the line as light green; The pointer moves to the next bar; }