SQL Server dynamic row to column

In the learning database, encountered how to transform the contents of the data table into a data table field, here, record their understanding of row to column

For example, there is a student score table:

Stuid: School Number

Course: Subjects

Score: Achievements

The contents of the table are:

STUID Course Score

0101 Languages 78

0101 Mathematics 90

0101 English 67

0101 Physics 88

And what we want is similar to the following table:

Stuid Chinese Mathematics English Physics

0101 78 90) 67 88

This time you need to use row to column, row to column with dynamic and static points:

Static row to column: Through the SQL statement, static conversion, once the original table data changes, such as increase chemical performance, or delete physical results, we have to re-change the SQL statement:

In the table above, the converted SQL statement is:

Select Stuid,

Max( CaseCourse when 'language'  ThenScoreElse 0 End) languages,Max( CaseCourse when 'Mathematics'  ThenScoreElse 0 End) Mathematics,Max( CaseCourse when 'English'  ThenScoreElse 0 End) English,Max( CaseCourse when 'Physical'  ThenScoreElse 0 End) Physical fromScores--Table name Group  byStuid--Group Queries

Main knowledge: Max (), Case,group by group query.

Max () takes the maximum value.

Case: My understanding is to choose from a few options, such as:

Case course when ' language ' then score else 0 end
When course is a language, case returns one of the corresponding score and 0, in this example, the first data is queried:

0101 Languages 78
At this time: course= ' language ', score=78, then case returns 78,
When querying the second piece of data:
0101 Mathematics 90
Course= ' language ' does not exist, then returns 0 (else 0)
And so on:

Max (78,0,0,0),
Max () takes the maximum, and the last data is 78,

The so-called static, is that we manually static access to each field (language, mathematics, English, physics), once the subject has changed, we have to modify the SQL statement, not very convenient

While dynamic row-to-column can avoid this situation, it is dynamic for itself to get the field name according to the data in the original table:

Declare @sql varchar(8000)--Declare a variable @sql, the data type is varchar (8000)Set @sql='Select Stuid,'--use set to assign value to @sqlSelect @sql =@sql +'Max (case course when" "+Course+" "Then score Else 0 end)'+" '"Course+" ",' from(Select distinctCourse fromScores asSC--Using SelectAssigning Values to @sqlSet @sql =left(@sql,Len(@sql)-1)+'From scores GROUP by Stuid'exec(@sql)--Execute @sql

Note: In an SQL statement, use single quotation marks to determine the range of the string, if the string itself contains single quotes such as: ' Name: ' Zhang San ', Gender: ' Male ', this time needs to use ', that is, two single quotation marks to represent the single quotation mark of the string itself.

Set statements you should be familiar with assigning values to variables, while select can actually be considered an assignment keyword, but a circular assignment (personal understanding).

For example: Select Stuid from students, which indicates that all Stuid attribute values in the students table (1,2,3,4 ...) are assigned to the variable stuid:

Stuid=1, for STUID operations (such as output stuid=1)

stuid=2, for STUID operations (such as output stuid=2)

Stuid=3, for STUID operations (such as output stuid=3)

Stuid=4 .....

Therefore, the Select Assignment statement in the dynamic row to column above can be interpreted as:

The results from the back of the (select course from scores) are assigned to the course variable one at a time, and the operation after each copy is: string connection

So

Select @sql [email protected] + ' max (case course if ' +course + ' then score else 0 End '+ ' + ' + ' + ', ' from (Selec T distinct course from scores) is executed as follows:
@sql = ' Select Stuid, ' + ' max (case course when ' + ' + language + ' then score else 0 end) ' + ' ' + ', '  [email protected]
@[email protected]+ ' Max (case course when ' + math + ' then score else 0 end) ' + ' ' math + ', '   [email protected]

@[email protected]+ ' Max (case course when ' + English + ' then score else 0 end) ' + ' ' English + ', '   [email protected]

@[email protected]+ ' Max (case course when ' + physical + ' then score else 0 end) ' + ' physical + ', '   [email protected]
Is it like the static code? Because they all have the same principle: max (), case,group by, different dynamic row-to-column using dynamic splicing string method, dynamic from the original table to find out the fields we need, if the original table deleted the physical results, we can not find the physical results, naturally will not be the subject ' Physics ' added to the results, if the original table added chemical results, we can also find the chemical score, and add it to the results, and finally, the EXEC statement execution @sql, this is the dynamic row-to-column.

SQL Server dynamic row to column