Current location - Training Enrollment Network - Mathematics courses - How to convert columns in sqlserver into rows, convert rows into columns and display them.
How to convert columns in sqlserver into rows, convert rows into columns and display them.
Create database arron

go to

Use arron

go to

-Create table initialization data

Create table students (

Name varchar(25),

Varchar class (25),

Grade integral

)

Insert into students' values (Zhang San, Chinese, 20)

Insert students' values (Zhang San, Mathematics, 90)

Insert into students' values ("Zhang San", "English", 50)

Insert into student value ('Li Si',' China', 8 1)

Insert students' values (Li Si, Mathematics, 60)

Insert into students' values ("Li Si", "English", 90)

-solution 1

Choose from students *

Pivot (

Maximum (grade)

For [class] in ([Chinese], [Mathematics], [English])

) as pvt

-Solution 2 is equivalent to self-connection.

Choose a. name, a. grade is language, b. grade is math, and c. grade is English.

From student A, student B and student C.

Where A.Name=B.Name and B.Name=C.Name

And Chinese and math.

And C.class=' English'

-Solution 3

Select a name,

Max (s.class =' Chinese' then s.graded end) as a language,

Max (s.class =' mathematics' then S. gradient end) as mathematics,

Max (when s.class=' English' and then s.grade end) is English.

From student group by name

-As far as identity is concerned.

Create Table Student 2 (

Id int primary key identification (1, 1),

Name varchar(25),

Varchar class (25),

Grade integral

)

Insert into student 2 value ('Zhang San',' Chinese', 20)

Insert into student 2 value ("Zhang San", "Mathematics", 90)

Insert into student 2 value ("Zhang San", "English", 50)

Insert into student 2 value ('Li Si',' Chinese', 8 1)

Insert two values ("Li Si", "Mathematics", 60) into the students.

Insert two values ("Li Si", "English", 90) into the students.

-Original solution 1 (problem)

Select from student 2 *

Pivot (

Maximum (grade)

For [class] in ([Chinese], [Mathematics], [English])

) as pvt

-Original solution 2 (ok)

Choose a. name, a. grade is language, b. grade is math, and c. grade is English.

From student a, student 2 B and student 2 C.

Where A.Name=B.Name and B.Name=C.Name

And Chinese and math.

And C.class=' English'

-Original solution 3 (ok)

Select a name,

Max (s.class =' Chinese' then s.graded end) as a language,

Max (s.class =' mathematics' then S. gradient end) as mathematics,

Max (when s.class=' English' and then s.grade end) is English.

From student group by name

-unpivot function use

Create table test 1(id int, name varchar(20), Q 1 int, Q2 int, Q3 int, Q4 int).

Insert into test 1 value (1,' a', 1000, 2000, 4000, 5000).

Insert into test 1 value (2,' b', 3000, 3500, 4200, 5500)

-sql implemented

select * from test 1

Select id, [name], [Du Ji] and [little beast] from test 1

Cancel perspective

(

The little beast represents Du Ji.

([q 1]、[q2]、[q3]、[q4])

)

As f

-The following sql can replace the above sql.

Select id, [name],

Du Ji ='Q 1',

Beast = (choose Q 1 from test 1, where id=a.id).

From test 1

alliance

Select id, [name],

Du Ji ='Q2',

Beast = (choose Q2 from test 1, where id=a.id)

From test 1

alliance

Select id, [name],

Du Ji ='Q3',

Beast = (choose Q3 from test 1, where id=a.id)

From test 1

alliance

Select id, [name],

Du Ji ='Q4',

Beast = (select Q4 from test 1, where id=a.id)

From test 1