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