Table Data transpose

In the data processing of the database, we sometimes need to swap rows and columns of data to convert the data in the form of the following

 

 

Convert to the following format:

 

 

Next let's take a look at the implementation in SQL Server 2000 and SQL Server 2005 respectively.

First, create a test environment.

 

 

 

The implementation in SQL Server 2000 is as follows:

 

 

 

Although this can meet the data requirements in our current table, if the student adds another exam subject

For example, in politics, the SQL we wrote may change. Can I write a general method?

After observation, we can

 

 

A summary can be implemented using dynamic SQL statements.

 

 

 

Through the above statements, we can achieve our goal and have general versatility.

The following describes how we achieve in SQL Server 2005, SQL Server 2005 on the T-SQL has been enhanced

BytesThe syntax provided is simpler and more readable than the syntax specified in a series of complex Select... case statements.

We can use the following SQL statement to implement

 

Select * from Fig (sum ([Mark]) for [course] In ([language], [mathematics], [English]) as PVT