SQL statement used to convert SQL Server database rows to columns)

Problem description

Assume that there is a student summary table (CJ) as follows:
Name subject result
Zhang San Chinese 80
Zhang San, mathematics 90
Zhang San physical 85
Li Si Chinese 85
Li Si mathematics 92
Li Si physical 82

If you want to write an SQL statement, the query result is
Name, Chinese, Mathematics, Physics
Zhang San 80 90 85
How can I achieve this?

Research Significance

This is not a complex problem, but it is a typical example of database row-to-column conversion. As long as we thoroughly study this abstract universal problem, other similar complex problems can be solved.

Problem Analysis

First, we will introduce the concept of downlink column conversion. This concept may not exist in the book. Row-to-column conversion involves the following issues: sometimes, to meet the user's Dynamic Requirements for database table design (such as adding a field), we use a table that defines the field name and then defines a field value, in this way, static data is used to express dynamics. In other words, the database table should have been horizontally extended into a vertical one, in other words, we should convert the field increase in the database table to the increase in the number of records. However, this design is flexible and makes statistical analysis difficult because it should be presented in an intuitive way. In other words, during statistical analysis, we should show more fields. If the column addition is converted to the row addition during data storage, and the column added data can be obtained during data extraction, this design of the database table will be transparent to users.

The problem raised earlier in this article is that during data extraction, data in the form of adding rows should be converted into data in the form of adding columns. Why? Note that the content in the subject field is stored in the database in the form of different data rows. In other words, it is added as rows, the content here will be changed to the field name.

There are several criteria for measuring the solution to this problem: 1. when the data exactly looks like this, can the solution be correctly solved? 2. if you have added the types of subjects, can the solution still work? 3. if the scores of a certain course have not yet been obtained, in other words, not everyone can find the scores of each course in the database, and the database lacks the records of the scores of a certain course. In this case, can the program still get reasonable results.

Test Environment

This test uses the ms SQL Server 2005 environment.

Test process

1. Create a data table and input data

Create Table [DBO]. [CJ] (
[Name] [varchar] (50) Collate chinese_prc_ci_as not null,
[Subject] [varchar] (50) Collate chinese_prc_ci_as not null,
[Result] [int] Null,
Constraint [pk_cj] primary key clustered
(
[Name] ASC,
[Subject] ASC
) With (ignore_dup_key = OFF) on [primary]
) On [primary]

Use a visual interface or an insert statement to input data

2. First, the most direct and simple method

Select distinct C. [name] as name,
(Select result from CJ where [name] = C. [name] and subject = 'China') as language,
(Select result from CJ where [name] = C. [name] and subject = 'mate') as mathematics,
(Select result from CJ where [name] = C. [name] and subject = 'physical ') as physical
From CJ C

The main idea is to divide the task into two steps. Step 1: generate the first column. Step 2: Based on the name values of each row in the first column, query the scores of each subject and join them to only one list generated in the first step. Distinct cannot be omitted.

This method can complete this task, but can only meet the evaluation criteria 1 and 3 described above. When the number of subjects increases or the number of actual subjects is not so large, the results are not perfect. In other words, this method is static, and the subject is written to an end in an SQL statement. In addition, it seems that the query efficiency of Several SQL statements in the middle is not so high. You also need to scan the entire table. In fact, you only need to find the corresponding records of a student.

3. Better methods

Regardless of Standard 2, think about whether it can solve the scanning efficiency problem. The following method is obtained.

Select [name] as name,
Sum (case when subject = 'China' then result end) as language,
Sum (case when subject = 'mate' then result end) as mathematics,
Sum (case when subject = 'physical 'Then result end) as physical
From CJ group by [name]

This method is similar to the previous one. The biggest improvement is the use of group by. Since the field names after group by cannot be directly used except for those of group by, a set function is added. In fact, this sum will only add one, because the primary key of this table is name + subject. If group by is used, the scanning efficiency is solved, because sum is calculated for each group. The trick of this method is the use of case when.

This method still cannot meet Standard 2.

4. A perfect solution

Now is how to solve the subject "from death to life" problem. You have come up with the following method:

Declare @ s nvarchar (1000)
Select @ s = 'select [name] As name'
Select @ s = @ s + ', sum (case when subject = ''' + Cast (subject as varchar) + ''' then result end) as '+ subject from CJ group by subject
Select @ s = @ s + 'from CJ group by [name]'
Exec (@ s)

In fact, the idea is based on the previous method. The key is to dynamically generate SQL statements and then execute them.

In the code of @ s's first accumulation, a sentence from CJ group by subject is very skillful. It can be seen that a simple select * from table t where .. this is also a constant change, so I have to admire the wisdom of SQL or relational databases.