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

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.