Cross-table, row-and-column conversions and cross-query classics

Cross-table, row-and-column conversions and cross-query classics

One, what is a cross table a Crosstab object is a grid that is used to return a value based on a specified condition. The data is displayed in compressed rows and columns. This format is easy to compare data and to identify trends. It consists of three elements: rows in the row column summary field "crosstab" extend horizontally (from one side to the other). In the example above, "glove" (gloves) is a row. columns in crosstab extend vertically (up and down). In the example above, the USA is a column. summarizes the intersection of the row and columns. The value at each intersection represents a summary (sum, count, and so on) of records that meet both row conditions and column conditions. In the example above, the value for the "gloves" and "us" crosses is four, which is the number of gloves sold in the United States. A crosstab can also include several totals: The end of each line is the total for that row. In the above example, the total represents the sales of a product in all countries/regions. The value at the end of the glove line is 8, which is the total number of gloves sold in all countries/regions. Note: The Totals column can appear at the beginning of each row. the bottom of each column is the total for that column. In the above example, the total represents the sales of all products in one country/region. The value at the bottom of the "United States" column is four, which is the total number of products (gloves, belts and shoes) sold in the United States. Note: The Totals column can appear at the top of each row. The total column (product total) and the Total row (total) are at the intersection of the totals. In the example above, the value at the intersection of the total column and the Total row is 12, which is the total number of all products sold in all countries/regions. second, row and column conversion and cross-query: 1: Column to row: eg1: assume that there is a student score sheet (CJ) as follows name      subject          result John          Chinese               John          Math               John           Physics              Dick           Languages              Dick          Mathematics               Dick          Physics               Related SQL statements: Create table CJ (name char (), subject char (), result int); Insert into CJ (Name,subject,result) VALUES (' John ', ' Language ', 99); Insert into CJ (Name,subject,result) VALUES (' John ', ' math ', 86); Insert into CJ (Name,subject,result) VALUES (' John ', ' English ', 75); Insert into CJ (Name,subject,result) VALUES (' Dick ', ' language ', 78); Insert into CJ (Name,subject,result) VALUES (' Dick ', ' math ', 85); Insert into CJ (Name,subject,result) VALUES (' Dick ', ' English ', 78) SELECT * FROM CJ Want to become like the next table Name Chinese mathematics Physics Zhang 399 90 85 Lee 485 Let's start by looking at how to create a static crosstab, which is a fixed list of rows, which can be done with a simple select query: Select Name,sum (case when a.subject= ' language ' then result else null end) as "language", SUM (case when a.subject= ' math ' then result else null end) as "mathematics", SUM (case when a.subject= ' English ' then result else null end) as "English" From CJ A Group by name; When you want to increase the total column: Total score, as shown in the following table: Total names of Chinese mathematical physics Zhang 3,260 99 90 85 Lee 4,241 Just add sum (a.result) as "Total Score", SQL as follows: Select Name,sum (A.result) as "Total Score", SUM (case when a.subject= ' language ' then result else null ') as "language", SUM (case when a.subject= ' math ' then result else null end) as "mathematics", SUM (case when a.subject= ' English ' then result else null end) as "English" From CJ A Group by name; It uses the case statement to judge, if it is the corresponding column, take the CJ value that needs to be counted, otherwise take null, then total. There are two FAQ notes: A, using NULL instead of 0 makes sense, if you use 0, although the SUM function sum can be taken to the correct number, but similar to the Count function (the number of records), the result is incorrect, because NULL is not a record, and 0 to calculate, the same empty string (""), in short, should be used here null, So any function is fine. b, if the design interface in the view to save the above query, then the error "No output column", which can not be saved, in fact, as long as the query in front of a paragraph: Create View ViewName as ..., ViewName is the name you are prepared to give the query, ... Is our query, and then run it, you can generate the view, for some other designers do not support the syntax, you can save this. The above query function is also very large, for many cases, such as the product sales table in accordance with quarterly statistics, according to the monthly statistics, such as column header content fixed situation, but often most of the following header content is not fixed, like city, users may at any time delete, add some cities, This situation is what we call a dynamic crosstab, which we can solve with a stored procedure in SQL Server. Below we add some knowledge: Related subqueries The difference between a correlated subquery and a normal subquery is that the related subquery references the columns of the outer query. This ability to refer to an external query means that the dependent subquery cannot run independently of itself, and that the external query reference makes it impossible to perform properly. Therefore, the execution order of the correlated subqueries is as follows: 1. Perform an external query first 2. Execute the subquery for each row of the external query separately, and reference the value of the current row outside each time the subquery is executed. Use the results of a subquery to determine the result set for an external query. Give an example; SELECT T1.type From titles T1 GROUP by T1.type Having MAX (t1.advance) >=all (SELECT 2 * AVG (t2.advance) From titles T2 WHERE T1.type = t2.type) This result returns the type of book with the highest prepayment exceeding twice times the average prepayment in a given group. Another example: Requires the maximum value of each number to be returned (list Id,name,score) ID Name (number) Score (score) 1 a 88 2 B 76 3 C 66 4 C 90 5 B 77 6 a 56 7 B 77 8 C 67 9 a 44 SELECT * FROM t a where score= (select Max (Score) from T b where a.name=b.name) And give a ranked SQL statement SELECT ( SELECT COUNT (*) 1 as DD From [Test] as a where a.[f2]<b.[f2]) as ORD,B.[F1], B.[F2] From [Test] as B Order by B.[F2]; All right, here's the related subquery about SQL first. local variable assignment method in SQL Server There are two kinds: One: SET @ variable name = value Two kinds: SELECT @ Variable name = value The second way you can get data from a table and then assign it to a variable Example: The user's name in the query from the User Information table that CID is 20 assigns him to the variable name DECLARE @name varchar (10)--User name Select @name =username from userInfo where cid = 20 print ' CID 20 user name: ' + @name Recursive Select variable A recursive select variable is the concatenation of a variable with itself using a SELECT statement and a subquery. The syntax forms are as follows: Select @variable = @variable + table.column from table---see SQL server2000: P354, This is a very graceful query method. This changes the vertical column data in the underlying table to horizontal data. This allows you to override the cursor. The dynamic crosstab replaces the traditional cursor. SQL statement Resolution: Writing a: DECLARE @sql varchar (4000) Set @sql = ' Select Name ' Select @sql = @sql + ', sum (case subject when ' ' +subject+ ' "' then result end) as ' +subject From (select distinct subject from CJ) as a Select @sql = @sql + ' from CJ Group by name ' EXEC (@sql) Writing two: DECLARE @sql varchar (4000) Set @sql = ' Select Name ' Select @sql = @sql + ', sum (case subject when ' ' +subject+ ' "' then result end) as ' +subject + ' from CJ GROUP by subject Select @sql = @sql + ' from CJ Group by name ' EXEC (@sql) A variety of different ways to see this article related links to other examples in the article transform is also available in access to achieve row and column conversions TRANSFORM count (Result) as number SELECT Name From Student performance form GROUP by name PIVOT Subject; TransForm usage is as follows: ========================================================= TRANSFORM aggfunction Selectstatement PIVOT PivotField [In (value1[, value2[, ...)]] The TRANSFORM statement can be divided into the following sections: Partial description Aggfunction the SQL aggregate function that operates on the selected data. Selectstatement SELECT statement. PivotField the field or expression to use when creating a column header in a query's result set. Value1, value2 is used to create a fixed value for the column headers. Description When you use a crosstab query to digest data, select a value from the specified field or expression as the column heading. This allows you to observe the data in a more compact format than the selected query. TRANSFORM is optional, but it is used as the first statement in the SQL string. It appears before the SELECT statement (which specifies the field that is the row header) and also appears in the GROUP by clause Before the specified row is grouped. You can optionally include other clauses, such as the WHERE clause, which specifies the attached Plus the selection or sorting criteria. You can also use a subquery as a predicate, especially in a WHERE clause of a fork table query. The value returned by PivotField is used as the column heading in the query result set. For example, in a crosstab query, 12 columns are created per sales chart by sales month. You can restrict PivotField to create a caption with a fixed value (value1, value2) that is listed in the optional in clause. You can also create additional columns with a fixed value that has no data. 2. Column row Conversion Temporarily reserved 3. Row-and-column conversion-combined There are table A, ID PID 1 1 1 2 1 3 2 1 2 2 3 1 How to make Table B: ID PID 1 1,2,3 2 1,2 3 1 To create a merged function Create function Fmerg (@id int) Returns varchar (8000) As Begin DECLARE @str varchar (8000) Set @str = ' Select @str = @str + ', ' +cast (PID as varchar) from table A where id= @id Set @str =right (@str, Len (@str)-1) Return (@str) End Go --Call the custom function to get the result SELECT DISTINCT Id,dbo.fmerg (ID) from Table A

RELATED Links:

SQL statement that turns the column into a row: http://blog.csdn.net/liaoxiaohua1981/archive/2006/05/30/763721.aspx
Implementing row and column conversions using SQL crosstab: http://blog.csdn.net/sivee/archive/2007/05/06/1598039.aspx
Oracle row and column conversions: http://blog.csdn.net/gogogo520/archive/2005/10/10/498779.aspx
Examples of row and column conversions: http://blog.csdn.net/zsl5305256/archive/2006/12/05/1430422.aspx
Examples of dynamic SQL usage, row and column conversions: http://blog.csdn.net/hertcloud/archive/2007/04/05/1552626.aspx
How SQL Server generates a dynamic crosstab query: http://dev.csdn.net/article/12/12618.shtm
SQL Statement Classic Collection http://hi.baidu.com/suofang/blog/item/35de9d23af3e5945ad34de8a.html