Database row and column conversion issues

In the usual work or interview, we may have encountered the database row and column conversion problem. Let's talk about it today.

1. Create a table

Let's start by creating a table.

SQL statements:

--1. Create data table if OBJECT_ID (' score ') is not null drop table Scorecreate table score (    name nvarchar (+),    course nvarchar (+), 
    score int) insert INTO score values (' Zhang San ', ' language ', 98) insert INTO score values (' Zhang San ', ' math ', ' ') insert into score values (' Zhang San ', ' physics ', INSERT into score values (' John Doe ', ' language ', ') insert into score values (' John Doe ', ' math ', ') insert into score values (' John Doe ', ' physical ', 100) SELECT * FROM Score

Execution Result:

2. Traditional row and Column conversion 2.1 longitudinal table to horizontal table

Let's take a look at the horizontal sheet we're going to turn into:

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.

Sql:

Select T. Name 2 from score as T 3 Group by T. Name

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. He 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

The language score is simple:

Select T. Name, SUM (Case T. Course when ' language ' then T. Score else 0 end) as language from score as Tgroup by T. Name

Results:

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

Full sql:

Select T. Name, sum (Case T. Course when ' language ' then T. Score else 0 end) as language, SUM (case T. Course when ' math ' then T. Score else 0 end) as math, SUM (ca Se t. course when ' physical ' then T. Score else 0 end) as physical from score as Tgroup by T. Name

OK, here we have the traditional way of the longitudinal table to the table is done.

2.2 Horizontal table to longitudinal table

So can we convert the converted horizontal table back?

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

--Converted tables Insert a new table select T. name, sum (Case T. The course when ' language ' then T. Score else 0 end) as language, SUM (t. course when ' math ' then T. Score else 0 end) A S mathematics, SUM (case T. Course when ' physical ' then T. Score else 0 end) as physical into Scorehbfrom score as tgroup by T. Name

At this moment Scorehb is the one we just converted, we'll find a way to turn him back.

How do you turn it? Step by step. We also first find out the language scores of Zhang San and John Doe.

Sql:

--Dick and Harry Chinese score select T. Name, ' language ' as the course, T. Language as fraction from SCOREHB as T

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.

Sql:

--UNION ALL link 3 subjects select T. Name, ' language ' as the course, T. Language as fraction from SCOREHB as Tunion allselect T. Name, ' math ' as the course, T. Math as score from SCOREHB As Tunion allselect T. Name, ' physical ' as course, T. Physical as fraction from SCOREHB as Torder by T. Name Desc

Results:

In this way, we will change the table back again.

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

3. Pivot and UNpivot operator for conversion

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.

3.1 Pivot longitudinal table turn Horizontal table

Sql:

Select    T2. Name,    T2. Mathematics,    T2 physics,    T2. Language from score as T1pivot (sum (fractional) for course in (Math, language, physics)) as T2

Results:

Is the code much more concise.

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.

3.2 Unpivot Horizontal table to the longitudinal table

Since there is a privot can be a longitudinal table to the horizontal table. So are there any operators that can help us turn back?

The answer is yes, he's unpivot.

Sql:

Select     * from Scorehb Unpivot (fractional for course in (language, mathematics, physics)) as T4

Results:

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

is not more than our previous table query stitching, convenient a lot.

Database row and column conversion issues