The student table has three columns, namely, name, course, Grade
Name Curricula Mark
Zhang San language 70
John Doe Mathematics 80
Dynasty English 59
Cheng Nan Ma zhe 70
Dynasty Language 90
The effect I want to get is to list the names of people who have passed the disciplines:
Chinese Chemistry Mathematics
Dick and Harry
Dynasty
There are more than 3 disciplines, and there may be eight of them. In fact, this is the typical dimensional direction change.
Prepare the data:
create table Stgrade (Name varchar (10) , curricula varchar (ten), Mark int);
go
insert into Stgrade values (' Zhang San ', ' language ', ' ' n ');
insert into Stgrade values (' John Doe ', ' mathematics ') , ' n ');
insert into Stgrade values (' Dynasty ', ' English ') , ' I ');
insert into stgrade values (' City South ', ' ma Zhe ') , ' n ');
insert into Stgrade values (' Dynasty ', ' language ') , ' n ');
go
SELECT * from Stgrade;
Namecurriculamark
Zhang San language 70
John Doe Mathematics 80
Dynasty English 59
Cheng Nan Ma zhe 70
Dynasty Language 90
The case is used to implement the value of the column according to the column value, because we do not need fractions here, only care about curricula and name, so after the score race is selected, a case when operation.
Select
Case when curricula= ' language ' then name end language,
Case when curricula= ' math ' then name end math,
case when Curricula= ' English ' Then name End English,
case when Curricula= ' ma zhe ' then name end Ma Zhe
From Stgrade where mark>59
Chinese maths English Ma Zhe
Zhang San Nullnullnull
Null John Doe Nullnull
Nullnullnull City South
Dynasty Nullnullnull
It is also possible to see that the result set is already in its infancy, and if the null of each column disappears, then the value of the subsequent row can be added to meet our requirements.
Select
Case when curricula= ' language ' then name end language,
Case when curricula= ' math ' then name end math,
Case when curricula= ' English ' Then name end English,
Case when curricula= ' ma zhe ' then name end Ma Zhe,
SN
from
(
Select Curricula,name,mark,
row_number () over (partition by curricula order by name) SN
From Stgrade where mark>59
) T
Chinese maths English ma zhe sn
Nullnullnull City South 1
Null John Doe NULLNULL1
Dynasty NULLNULLNULL1
Zhang San NULLNULLNULL2
Select SN,
Max (case when curricula= ' language ' then name end) language,
Max (case when curricula= ' math ' then name end) Math,
Max (case when curricula= ' English ' then name end) English,
Max (case when curricula= ' ma zhe ' then name end) Ma Zhe
from
(
Select Curricula,name,mark,
row_number () over (partition by curricula order by name) SN
From Stgrade where mark>59
) T
GROUP BY SN
SN Chinese math English ma Zhe
1 dynasty John Doe Null city South
2 sheets of three Nullnullnull