How does sqlserver generate dynamic cross tabulation queries?

VB + MS sqlserver is the most commonly used mode for developing database application systems. I have reviewed my previous posts and asked some questions about sqlserver, but I have very few correct answers, now I have posted my opinions on these issues. This time I will talk about the dynamic cross tabulation problem first.

To illustrate the problem, we use the instance database (northwind) provided by sqlserver for verification. All examples should be run in northwind. I may omit the use statement and the referenced table, all of them are in northwind. I will not describe them below

The cross tabulation I mentioned here is the same as the cross tabulation query of access. For example, the City field in the Employees table represents the name of the city, and the titleofcourtesy indicates the name, we want to calculate the combined count of the reportsto Field Based on the city and titleofcourtesy conditions (this statistics does not have any practical significance, but selects some fields that record repeated content to describe the situation ), the format is as follows: (titleofcourtesy serves as the row and city serves as the column)

Titleofcourtesy	Londoncity	Redmondcity	Seattlecity
Dr.
Mr.	12
Mrs.		2
Ms.	5		4

Before discussing this issue, let's first take a look at how to create a static cross table, that is, a fixed cross table with a fixed number of columns. In fact, this can be done with a simple SELECT query:

Select titleofcourtesy,
Sum (Case City when 'London 'Then reportsto else null end) as [London City],
Sum (Case City when 'redmond 'Then reportsto else null end) as [Redmond City],
Sum (Case City when 'seattle 'Then reportsto else null end) as [Seattle City]
From employees group by titleofcourtesy

The case statement is used to determine. If the column is corresponding, The reportsto value to be calculated is used. Otherwise, null is used and then the total value is used.
Two common problems are described as follows:
A. It makes sense to use null instead of 0. If 0 is used, although sum can obtain the correct number, the result is incorrect, even if the sum function is similar to count function (number of records, null is not a record, and 0 is counted. Similarly, the Null String ("") is used. In short, null should be used here, so that no function can be used.

B. if you save the preceding query on the design interface of the view, an error "no output column" will be reported, which cannot be saved. In fact, you only need to add a section before the query: create view viewname ..., viewname is the name you want to query ,... it is our query, and then we can run it to generate a view. For the syntax not supported by other designers, we can also save it like this.

The preceding queries are also very useful. For many situations, such as quarterly statistics and monthly statistics, the column header content is fixed, however, in most cases, the following headers are not fixed, such as city. Users may delete and add cities at any time. In this case, we need to use stored procedures to solve the problem:

The general idea is actually very simple. First, retrieve the column header information to form a cursor, traverse the cursor, and replace the content of the case judgment in the preceding query statement with the value in the cursor, create a new SQL query, execute the query, and return the result. Here is a stored procedure that I wrote for your reference:

Create procedure corsstab
@ Strtabname as varchar (50) = 'ployees', -- put the table name here
@ Strcol as varchar (50) = 'city', -- header grouping based on fields
@ Strgroup as varchar (50) = 'titleofcourtesy ', -- group field
@ Strnumber as varchar (50) = 'reportsto ', -- calculated Field
@ Strsum as varchar (10) = 'sum' -- Calculation Method
As

declare @ strsql as varchar (1000), @ strtmpcol as varchar (100)
execute ('Clare corss_cursor cursor for select distinct '+ @ strcol + 'from' + @ strtabname +' for read only ') -- generate a cursor
begin
set nocount on
set @ strsql = 'select' + @ strgroup + ', '+ @ strsum +' ('+ @ strnumber + ') as ['+ @ strsum +' of '+ @ strnumber +'] '-- the first half of the query

Open corss_cursor
While (0 = 0)
Begin
Fetch next from corss_cursor -- traverses the cursor and places the column header information in the variable @ strtmpcol
Into @ strtmpcol
If (@ fetch_status <> 0) Break
Set @ strsql = @ strsql + ', '+ @ strsum +' (case '+ @ strcol + 'when''' + @ strtmpcol + '''then' + @ strnumber + 'else null end) as ['+ @ strtmpcol + ''+ @ strcol +'] '-- construct a query
End
Set @ strsql = @ strsql + 'from' + @ strtabname + 'group by' + @ strgroup -- end of the query

Execute (@ strsql) -- execute

If @ error <> 0 return @ error -- if an error occurs, an error is returned.Code
Close corss_cursor
Deallocate corss_cursor return 0 -- release the cursor. If 0 is returned, the operation is successful.

End
Go

Notes:
A. This is a general stored procedure. When using the variables @ strtabname, @ strcol, @ strgroup, @ strnumber, and @ strsum, you can use other tables, the second column of the result set adds a total column.
B. For the convenience of testing, I set the default value in the stored procedure, that is, the Employees table mentioned above. In this way, the above results can be obtained during direct running.
C. during use, you can copy the above Code to the SQL pane of the query design interface of the Enterprise Manager, or run it in the query analyzer (ensure that the northwind database is selected correctly ), you can generate a stored procedure: corsstab, and then run corsstab directly. If a pane similar to the one before this article appears, the operation is successful.
D. If it is used for other tables, you must first generate the stored procedure in your user database (you can also put it in the master, and then add a variable: @ database, which is assigned the database name, then open the specified database in the code above, so that all databases can call it), when you call, take the following format:

Corsstab @ strtabname = 'Orders ', @ strcol = 'datepart' (YY, orderdate)', @ strgroup = 'mermerid', @ strnumber = 'orderid', @ strsum = 'Count'

The preceding statement counts the annual order quantity of each customer in the orders table in northwind. You can try it. Although the name displayed in the column header is incorrect, the basic result is displayed, I believe that by making simple modifications to my code, you can achieve satisfactory cross-tabulation results.

Next time I will talk about how to automatically add a row number to the queried record set.