Technical insider & lt; 2 & gt; Tables Operator

Example 1: In the help document of SqlServer, the functions of tables are interpreted as follows: You can use the operators between tables and unregister to operate on table expressions to obtain another table. Tables converts a table expression into multiple columns in the output by uniquely converting one column of the expression, and, if necessary, executes any other columns required in the final output.

Example 1: In the help document of SQL Server, the functions of tables are interpreted as follows: You can use the operators between tables and unoperators to operate on table expressions to obtain another table. Tables converts a table expression into multiple columns in the output by uniquely converting one column of the expression, and, if necessary, executes any other columns required in the final output.

Example 1:

In the help document of SQL Server, the explain function is explained as follows:
You can use the between operator and unregister relational operator to operate on the table value expression to obtain another table. CONVERT Converts a unique value in a column of an expression to multiple columns in the output table, aggregate any other column values required in the final output when necessary.

For friends who used the lift function for the first time, this explanation is difficult for everyone to understand. The following editor uses the lift function to implement a row-to-column conversion function, this makes it easier for readers to understand this function.

Note: forward is a new function of SQL Server2005. For details about how to forward the code to 2005, refer to this site:
SQL Server (Row-to-column conversion) Row-to-column conversion and column-to-row addition of average value and summary Value

Create a payroll first:

Create Table Salary
(
HrName varchar (50 ),
Monthly varchar (50 ),
Money money
)

Insert data into the table:

Insert into Salary (HrName, Monthly, [Money])
Select 'zhang san', 'August 11', '123'
Union all
Select 'zhang san', 'August 11', '123'
Union all
Select 'zhang san', 'August 11', '123'
Union all
Select 'Lee 4', 'August 11', '123'
Union all
Select 'Lee 4', 'August 11', '123'
Union all
Select 'Lee 4', 'August 11', '123'
Union all
Select 'zhang san', 'August 11', '123'

View normal data:

Select * from Salary

Result:

HrName Monthly Money
Zhang San, September
Zhang San, September
March
Li Si, September
Li Si, September
July
Zhang San, September

View the data after row-to-column conversion:

Select HrName as 'name', [January], [February], [March] from Salary
Aggregate (sum ([Money]) for Monthly in ([January], [February], [March]) as pvt

Result:

Name: January 1, January 2-January 1, March
Li Si 3800.00 4200.00 3900.00
Zhang San 5000.00 3200.00 500.00

Note:
Aggregate (sum ([Money]) for Monthly in ([January], [February], [March]) in sum ([Money]), here must be an aggregate function, for example, min or max.
In ([January], [February], [March]) [January], [February], [March] is the Value of Monthly, and is the new name of the result set.

If we change January to April, because the data source does not have a April record, it should be Null in April.
Test:

Select HrName as 'name', [April], [February], [March] from Salary
Aggregate (sum ([Money]) for Monthly in ([April], [February], [March]) as pvt

Result:

Name: January 1, April 2-January 1, March
Li Si NULL 4200.00 3900.00
Zhang San NULL 3200.00 3500.00

Example 2:

In the SQLServer 2000 environment, to implement cross-tabulation reports, it mainly relies on a series of complex SELECT... CASE statements.

For the implementation process, see here T-SQL Cross report (row and column Interchange) Cross query rotation Query

In SQLServer 2005, we can use the limit relational operator to convert rows and columns.

Take the student renewal table as an example:

Id name subject score

1 Zhang San Language 60
2 Zhang San, mathematics 65
3. three foreign languages 70
4. Li Si language 80
5 Li Si mathematics 90
6 Li Si Foreign Language 85
7. Wang Wu language 70
8. Wang Wu, mathematics 71
9 Wang Wu Foreign Language 75
10 Zhao Liu language 64
11 Zhao liumath 67
12 Zhao Liu Foreign Language 76

The query result is as follows:

Name Chinese Mathematics Foreign Language

Li Si 80 90 85
Wang Wu 70 71 75
Zhang San 60 65 70
Zhao Liu 64 67 76

-- Prepare data:

Select * from sysobjects where [xtype] = 'U'

Go

If exists (select id from sysobjects where name = 'studentscore ')

Drop table studentscore -- delete a table that conflicts with the experiment

Go

Create table studentscore -- create an experiment table

(

[Id] int identity (1, 1 ),

[Name] nvarchar (20) not null,

Subject nvarchar (20) not null,

Score int not null

)

Go

Select * from studentscore

Go

-- Add lab data

Insert studentscore values ('zhang san', 'China', '60 ');

Insert studentscore values ('zhang san', 'mat', '65 ');

Insert studentscore values ('zhang san', '', '70 ');

Insert studentscore values ('Li si', 'China', '80 ');

Insert studentscore values ('Li si', 'mat', '90 ');

Insert studentscore values ('Li si', '', '85 ');

Insert studentscore values ('wang wu', 'China', '70 ');

Insert studentscore values ('wang wu', 'mat', '71 ');

Insert studentscore values ('wang wu', '', '75 ');

Insert studentscore values ('zhao liu', 'China', '64 ');

Insert studentscore values ('zhao liu', 'mat', '67 ');

Insert studentscore values ('zhao liu', '', '76 ');

Go

Select * from studentscore

Go

Use the SELECT... CASE statement to implement the Code as follows:

Select [name],

Language = max (case

When subject = 'China' then score else 0

End ),

Mathematics = max (case

When subject = 'mate' then score else 0

End ),

Foreign Language = max (case

When subject = 'on' then score else 0

End)

From studentscore

Group by [name]

Result:

In the following example, we use the limit relational operator to convert rows and columns.

Select [name], [language] as 'China', [mathematics] as 'mat', [Foreign Language] as 'foreign language'

From (select score, subject, [name] from studentscore) as ss

Bytes

(

Sum (score) for subject in ([Chinese], [mathematics], [Foreign Languages])

) As pvt

Result: the cross-Table report is completed with less code.

======================================

Note that we use the sum () Aggregate Function to group data by name.

How can we do this? The original sequence relational operator will judge based on the column in the previous object from the row. In this example, the sequence object is ss and is a subquery. This subquery contains only three columns, score, subject and [name], but the score and subject columns are used inside the score operator, so it must be for [name] grouping.

Therefore, we can conclude that the grouping rule of the operator is to follow the columns in the object that are not inside the operator:

For better understanding, let's write another example:

-- In the ss subquery, add a column id.

-- Groups should be grouped by name and id.

Select [name], [language] as 'China', [mathematics] as 'mat', [Foreign Language] as 'foreign language'

From (select score, subject, [name], id from studentscore) as ss

Bytes

(

Sum (score) for subject in ([Chinese], [mathematics], [Foreign Languages])

) As pvt

Result: our ideas are verified.

From the literal perspective, the unordered relational operator knows that it is used in the opposite way. The following is an example:

If exists (select id from sysobjects where name = 'studentscore ')

Drop table studentscore -- delete a table that conflicts with the experiment

Go

Create table studentscore -- create an experiment table

(

[Id] int identity (1, 1 ),

[Name] nvarchar (20) not null,

Yuwen int not null,

Shuxue int not null,

Waiyu int not null

)

Go

Select * from studentscore

Go

-- Add lab data

Insert studentscore values ('zhang san', '60', '65', '70 ');

Insert studentscore values ('Li si', '80', '90', '86 ');

Insert studentscore values ('wang wu', '70', '71 ', '75 ');

Insert studentscore values ('zhao liu', '64', '67', '76 ');

Go

Select * from studentscore

Go

Result:

SELECT id, [name], subject, score

FROM

(SELECT id, [name], Chinese = yuwen, mathematics = shuxue, foreign language = waiyu

FROM studentscore) as ss

Unregister

(Score FOR subject IN

(Chinese, mathematics, foreign languages)

) AS unpvt

Result: