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

Source: Internet
Author: User
Tags case statement extend crosstab
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:

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)

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
And give a ranked SQL statement
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
From Student performance form
GROUP by name
PIVOT Subject;

TransForm usage is as follows:
TRANSFORM aggfunction
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.

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,
1 1
1 2
1 3
2 1
2 2
3 1

How to make Table B:
1 1,2,3
2 1,2
3 1

To create a merged function
Create function Fmerg (@id int)
Returns varchar (8000)
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)

--Call the custom function to get the result
SELECT DISTINCT Id,dbo.fmerg (ID) from Table A


SQL statement that turns the column into a row:
Implementing row and column conversions using SQL crosstab:
Oracle row and column conversions:
Examples of row and column conversions:
Examples of dynamic SQL usage, row and column conversions:
How SQL Server generates a dynamic crosstab query:
SQL Statement Classic Collection

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.