How to generate a dynamic crosstab to query FUXC (original) keyword crossed table SQL stored procedure
VB MS SQLServer, is the most common mode of our current development database application system, flip over the old post, there are some SQLServer problems frequently, but correctly answer less, now I have a test of these problems. , This time, first talk about the problem of dynamic crosstab
To illustrate the problem, we use SQLServer's own example database (Northwind) to verify, please put it in Northwind, I may omit the USE statement, the reference to the table is Northwind, I will below. No longer explain
I refer to the intersection of Access, such as the ACCESS's crosstab query, such as the City field in the Employees table represents the name of the city, TitleOfCourtesy represents the name of the ReportSto field according to the situation of City and TitleOFCOURTESY ( This statistically does not have any practical significance, just selecting some fields containing duplicate contents to explain the situation), and display it to the following format: (TitleOfcourtesy as a row, city as column)
TitleOfcourtesy Londoncity Redmondcity Seattle Dr. Mr. 12 MRS. 2 MS. 5 4
Before discussing this problem, let's take a look at how to build a static cross-table, that is, the number of fixed crosstables, this situation is actually as long as a simple SELECT query can get:
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 'Ten Reportsto else Null End AS [Seattle City] from Employees Group by TitleOfcourtesy
Among them, the CASE statement is used to determine if it is the corresponding column, then take the statistics of the ReportSto value, otherwise take NULL, then there are two common problems to explain: a, use null without 0, if it is reasonable, if With 0, although the sum of the function SUM can take the correct number, similar to the count function (taking the number of records), the result is not right, because NULL is not counting a record, and 0 should be calculated, the sympathist string (" ") Is also the case, in short, you should use null, so any function is no problem.
b, if you save the above queries in the design interface of the view, you will report an error "No output column", so you can't save it. Name, ... is our query, then run, you can generate a view, and you can save this for other designers.
The above query is also very large, for many cases, such as the statistics of the quarterly statistics, in accordance with the contents of the month statistics, this is, but often in most cases, the contents of the list are not fixed, like city, users may always Delete, add some cities, this situation, we need to use the stored procedure to solve: the overall thinking is actually very simple, first retrieve the list information, form a cursor, then traverse the cursor, will query the content of the Case judgment above The value is replaced, form a new SQL query, then execute, return the result, it is, the following is a stored process I wrote, for your reference:
Create Procedure Corsstab @strtabname As Varchar (50) = 'Employees' TitleOFCOURTESY ', - Packet field @Strnumber as varchar (50) =' reportsto ', - Statified field @strsum as varchar (10) =' sum '- Operation method AS
DECLARE @strSql as varchar (1000), @strTmpCol as varchar (100) EXECUTE ( 'DECLARE 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 segment
Open Corss_Cursor While (0 = 0) Begin Fetch Next from Corss_cursor - Traversing Cursor, put the list information into the variable @strtmpcol if (@@ fetch_status <> 0) BREAK SET @strsql = @strsql ',' @strsum '(Case' @Strcol 'when' '' @strtmpcol '' 'TEN' @StrNumber 'Else Null End) AS [' @Strtmpcol '' @strcol '] '- Construction query End set @strsql = @strsql ' from ' @strtabname ' group by ' @strgroup - Query End EXECUTE (@strsql) - Execution
IF @@ error <> 0 return @@ error - If an error, returning an error Code CLOSE CORSS_CURSOR DEAALLOCATE CORSS_CURSOR RETURN 0 - Release the cursor, return 0 means success
Endgo
Some explanations: a, this is a universal stored procedure, use @ stratabname, @ strnumber, @ strgroup, @ Strnumber, @ strsum several variable settings can be used in other tables, where the result set of the result set I Added a column B. For the convenience of testing, I set the default value during the stored procedure, which is the Employees table mentioned earlier, so you can run the result mentioned above. C. When using, you can copy the above code to the Query Design interface SQL pane of the Enterprise Manager, or the query analyzer run (Note Correctly select the Northwind database), you can generate a stored procedure: Corsstab, then run directly Corsstab, if there is a pane in front of this article, it means that the run is successful. D. If used for other tables, you first need to generate this stored procedure in your user database (of course, you can also put it in the master: @Database, assign the value of the database name, then open the specified database on the above code In this way, all databases can call it), when you call, take the following format:
Corsstab @strtabname = 'orders', @strcol = 'datepart (yy, orderdate)', @ strun = 'customerid', @strnumber = 'orderid', @strsum = 'count'
