columns of SQL (crossbar) conversions

Create a table scores

First, the traditional ranks of the transformation

longitudinal table to Horizontal table

The horizontal table we're going to turn into is this:

Since this table has only two columns, you can group by name. Put the name together first, and then we'll try to figure it out later.

Select  from

Results:

Analysis:

1. We get the score of the subject in Chinese first. Now that we've used the group by statement, there's a definite need for the aggregation function to get the score.
2. And we only need the results of this section of the language, grouped out of a total of 3 columns, respectively, language, mathematics, physics. Then you need to judge the subject to take the score.

Here comes the case statement that meets our needs. It has the same effect as switch-case in C #.

SQL Case Statement syntax:

 Case field when    value 1 then result when    value 2 then result 2    ... Else Default result End

Select name, SUM ( case Course when  ' language 'else0as   from

Results:

Now that the scores of the Chinese have been taken, the other subjects will be changed under the conditions.

Full sql:

Selectname, SUM ( CaseCourse when'language'Then fractionElse 0End aslanguage, SUM ( CaseCourse when'Mathematics'Then fractionElse 0End asMath, SUM ( CaseCourse when'Physical'Then fractionElse 0End asPhysical fromScores GROUP by name

Horizontal Table to longitudinal table

Let's first insert the table that we just turned into a new table Scores2.

Selectname, SUM ( CaseCourse when'language'Then fractionElse 0End aslanguage, SUM ( CaseCourse when'Mathematics'Then fractionElse 0End asMath, SUM ( CaseCourse when'Physical'Then fractionElse 0End asPhysics into Scores2 fromScores GROUP by name

We also first find out the language scores of Zhang San and John Doe.

Select' Chinese ' asasfrom

Results:

What about the data from two other subjects? It's simple, we're going to find each one, and then we'll use union ALL to combine them into a single table.

 Selectname,'language'  asCourses, Languages asscore fromScores2 Union AllSelectname,'Mathematics'  asCourses, Mathematics asscore fromScores2 Union AllSelectname,'Physical'  asCourses, Physics asscore fromscores2 ORDER BY name Desc

Results:

But do you find it troublesome? Don't worry, we have an easier way. The pivot relational operators are described below.

Pivot is an operator provided by SQL Server 2005, so it can be used as long as the database is above version 05. Primarily used for row and column conversions.

Pivot longitudinal table to horizontal table

Select     T2. Name,    T2. Mathematics,    T2 physics,    T2.   as T2

Pivot converts the data row in the course field in the original table to a column in math, language, and the value of the corresponding column with sum.

We just have to remember how it's used.

Unpivot horizontal table to longitudinal table

Select      from   for in as T3

Unpivot the language, mathematics, and physics columns into rows, and the scores for the new column store the corresponding values.

columns of SQL (crossbar) conversions