Table Data transpose

Source: Internet
Author: User

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

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.