SQL cross tabulation

SQL cross tabulation method cross tabulation-Cross is generally a type of grouping statistics, the form is more complex, display is more clear, however, the database itself does not provide the real-current cross tabulation function. To create a cross tabulation by yourself, you must be familiar with the process, cursor, temporary table, dynamic SQL, and so on, and -- and the idea should be clear, this example uses PUBS. DBO. data in the SALES table

SQL cross tabulation method cross tabulation-Cross is generally a type of grouping statistics, the form is more complex, display is more clear, however, the database itself does not provide the real-current cross tabulation function. To create a cross tabulation by yourself, you must be familiar with the process, cursor, temporary table, dynamic SQL, and so on, and -- and the idea should be clear, this example uses PUBS. DBO. data in the SALES table

Cross tabulation using SQL

-- Crossover is generally a type of grouping statistics. The display is more complex and clear, but the database itself does not provide the real-current cross table function, creating a crosstab chart by yourself is not only familiar with processes, cursors, temporary tables, and dynamic SQL statements, but also requires clear thinking. This example uses PUBS. DBO. sample Data in the SALES table: create procedure UP_TEST (@ T1 VARCHAR (30), @ T2 VARCHAR (30), @ T3 VARCHAR (30), @ T4 VARCHAR (30 )) AS--T1 table name, T2, T3 is the cross table on the two classification fields, T4 is the summary field -- T2 is the row field, T3 column field BEGINDECLARE @ SQL VARCHAR (7999 ), @ field varchar (30) SELECT @ SQL = 'select stinct '+ @ T3 + 'from' + @ T1 CREATE TABLE # FIELD (FIELD VARCHAR (30) -- convert column characters Segment extraction to temporary TABLE # insert into # field exec (@ SQL) SELECT @ SQL = 'create TABLE CROSS_TEST ('+ @ T2 +' VARCHAR (30 ), 'destare CUR_FIELD cursor local for select * FROM # field open CUR_FIELD FETCH CUR_FIELD INTO @ field while @ FETCH_STATUS = 0 begin select @ FIELD = '[' + @ FIELD + ']' SELECT @ SQL = @ SQL + @ FIELD + 'decimal (8, 2) DEFAULT 0, 'fetch CUR_FIELD INTO @ field end select @ SQL = LEFT (@ SQL, LEN (@ SQL)-1) + ')' -- create a temporary crosstab chart CROSS_TES T exec (@ SQL) SELECT @ SQL = 'insert INTO CROSS_TEST ('+ @ T2 + ') select distinct '+ @ T2 + 'from' + @ T1 -- store row data into a cross table # CROSS_TESTEXEC (@ SQL) -- CREATE a group data table temp select @ SQL = 'create TABLE TEMP ('+ @ T2 + 'varchar (30),' + @ T3 + 'VARCHAR (30 ), '+ @ T4 +' DECIMAL () 'EXEC (@ SQL) -- put the cross summary data into the cross table SELECT @ SQL = 'select' + @ T2 + ', '+ @ T3 +', SUM (QTY) qty from '+ @ T1 + 'group by' + @ T2 +', '+ @ T3 insert into temp exec (@ SQL) -- write the summary data to the cross table DECLARE CUR_S Um cursor local for select * from temp declare @ F1 VARCHAR (30), @ F2 VARCHAR (30), @ qty decimal (8, 2), @ Q1 VARCHAR (30) OPEN CUR_SUM FETCH CUR_SUM INTO @ F1, @ F2, @ qty while @ FETCH_STATUS = 0 begin select @ F2 = '[' + @ F2 + ']', @ Q1 = CAST (@ qty as varchar (30 )) SELECT @ SQL = 'Update CROSS_TEST set' + @ F2 + '=' + @ Q1 + 'where' + @ T2 + '= ''' + @ F1 + '''' EXEC (@ SQL) FETCH CUR_SUM INTO @ F1, @ F2, @ qty end close CUR_SUM SELECT * FROM CROSS_T Est drop table temp drop table CROSS_TEST drop table # FIELDEND--------------------------------------------------------EXEC UP_TEST 'sales', 'title _ id', 'stor _ id', 'qty '/* description: in order to process fields that contain special characters, brackets in the field are worth noting that the table that implements the cross tabulation must have two categories. In this example, only the data types of the classification fields are supported, the biggest problem is to highlight the WHERE condition of this line. When querying a character-type field, the condition must be enclosed in single quotation marks. If it is a numerical value, you can directly write it, therefore, classification fields of the value type are easier to implement and can be integrated in a process. The cross tabulation we see usually has information such as row summary and column summary, which is not implemented in this example. Let's work on your own. */