SQL converts rows and columns to facilitate data storage and presentation.

Source: Internet
Author: User

Since MS SQL Server 2005, Microsoft has introduced batch and unbatch to implement row-and-column conversion, which greatly facilitates data storage and presentation. Today, we will analyze these two keywords and explain how to store and present data with examples.
For example, there is a table in the Student Course Selection and score system, which stores the students' course scores. We cannot predict the number of courses. Therefore, the general table is designed as follows:
Figure 1
 
The last column is the course number, so no matter whether the course will be added for students to choose after the school starts, it does not matter. The reports to be presented to users are generally as follows:
Figure 2
 
We can see that columns are used for data storage, and row-based data is displayed. How can this problem be solved? The following is a detailed analysis:
Create table statement
Copy codeThe Code is as follows:
USE [master]
GO
/***** Object: Table [dbo]. [Table_1] Script Date: 08/06/2013 13:55:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Create table [dbo]. [Table_1] (
[Name] [varchar] (50) not null,
[Score] [real] not null,
[Subject_id] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Insert Test Data
Copy codeThe Code is as follows:
Insert into [master]. [dbo]. [Table_1] ([name], [score], [subject_id]) values ('zhang san', 90, 1 );
Insert into [master]. [dbo]. [Table_1] ([name], [score], [subject_id]) values ('zhang san', 80, 2 );
Insert into [master]. [dbo]. [Table_1] ([name], [score], [subject_id]) values ('zhang san', 70, 3 );
Insert into [master]. [dbo]. [Table_1] ([name], [score], [subject_id]) values ('wang wu', 50, 1 );
Insert into [master]. [dbo]. [Table_1] ([name], [score], [subject_id]) values ('wang wu', 40, 2 );
Insert into [master]. [dbo]. [Table_1] ([name], [score], [subject_id]) values ('Li si', 60, 1 );

Now, the data in Table_1 is the result in Figure 1. To get the result in Figure 2, use the following statement:
Copy codeThe Code is as follows:
SELECT [name], [1], [2], [3]
FROM [master]. [dbo]. [Table_1]
Bytes
(
Sum (score) for subject_id in ([1], [2], [3])
) As pvt
GO

If the database table is stored in figure 2 and needs to be displayed in Figure 1, uncommitted is required. You can do this:
Copy codeThe Code is as follows:
SELECT [name], [subject_id], [score]
FROM
(
SELECT [name], [1], [2], [3]
FROM [master]. [dbo]. [Table_1]
Bytes
(
Sum (score) for subject_id in ([1], [2], [3])
) As pvt
) P
Unregister
(
Score for subject_id in ([1], [2], [3])
) As unpvt

Of course, on the basis of Table_1, I first use pvt to convert it to row-based storage, and then use unregister to present it in the determinant.

Related Article

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: info-contact@alibabacloud.com 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.