Full parsing of SQL Server database data summary (WITH ROLLUP)

Existing table A has the following content:

Code repository quantity 01 A 6 01 B 7 02 A 8 02 B 9

Now you want to query this format by encoding:

01 A 6 01 B 7 Summary Subtotal: 13 02 A 8 02 B 9 Summary Subtotal: 17

Q: How can this problem be achieved?

At first glance, it seems very easy to use group? However, if you study it carefully, you will feel that group by is powerless and you will have nothing to do. So what should we do? Don't worry, SQL Server has already helped us. Next, come with me.

First, let's take a look:

The ROLLUP operator is useful when generating reports that contain subtotal and aggregate. The result set generated by the ROLLUP operator is similar to the result set generated by the CUBE operator.

The result set generated by the CUBE operator is a multi-dimensional dataset. A multi-dimensional dataset is an extension of fact data. Fact data is the data that records individual events. The extension is created on the columns that the user intends to analyze. These columns are called dimensions. A multi-dimensional dataset is a result set that contains all possible combinations of cross tables for each dimension.

The CUBE operator is specified in the group by clause of the SELECT statement. The statement selection list should contain dimension columns and aggregate function expressions. The dimension column and keyword with cube should be specified for group. The result set contains all possible combinations of values in the dimension column, and the aggregated values in the basic rows that match the combination of these dimension values.

The difference between CUBE and ROLLUP is:

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

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

After reading the above passage, what have you realized? If not, then ...... Hey, your understanding is not enough. It's still early to go to the "Three flowers" Summit :). Next let's take a look (note that the answer will be announced soon ):

SELECT Code, warehouse, Sum (Quantity) as number from a group by code, warehouse WITH ROLLUP

-- The key is the following WITH ROLLUP

-- Of course, you can also use with cube, but the results will be slightly different.

After reading the above section, you may still find yourself confused. It doesn't matter if you don't understand it. Do it yourself.

First, create A table A mentioned above and input several rows of data;

Next, open your SQL Server Query analyzer, connect the Server that contains the table A you created above, and select the database that contains the table;

Then: Copy the preceding SQL statement, Paste to the query analyzer, press F5, how? What is shown below? Is it the same as below?

Code repository quantity 01 A 6 01 B 7 01 NULL 13 02 A 8 02 B 9 02 NULL 17 NULL 30

-- If you use with cube, there will be two more rows after the result set (if you only enter several rows of data in the example ):

NULL A 14 NULL B 16

Success! How can there be so many "NULL" values in the results? Ha, don't worry. These rows are exactly the data rows we want to summarize. It's not hard to see:

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

NULL 30 is a summary of the number of data rows.

How? What is the answer? Is it easy? Of course, there are still some shortcomings above, that is, there are many "NULL. How can we remove meaningless NULL values? Next we will optimize it.

1. Replace NULL with Grouping

Select case when (GROUPING (encoding) = 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 number from a group by encoding, warehouse WITH ROLLUP

-- Use Case functions appropriately

I will not write it here, that is, replace ALL the above "NULL" values with the "ALL" string.

2. Use the program for further optimization

// We must optimize the results generated by the preceding SQL statement to display the requirements. The following describes the natural language:

WHILE (last record not reached) {IF encoding value is not ALL and warehouse value is ALL {Replace the encoding value with "Subtotal:" And warehouse value; the color of this line is marked as gray;} The ELSE encoding value is also the ALL warehouse value is also the ALL {Replace the encoding value with "Total:" and the warehouse value; mark this row as light green;} Move the pointer to the next one ;}