SQL displays rows as columns

When I wrote this article, I referred to the book "SQL hacks", which uses SQL Server 2008.

For example, there is a table

create table  tbCourseGrade(student varchar(10)  , course varchar(10)  , grade int )

Then there are some records:

Insert into tbcoursegrade (student, course, grade) values ('James ', 'high number', 98), ('James', 'English ', 56), ('James ', 'China', 77), ('small red', 'high data', 89), ('small red', 'English ', 88), ('xiaoming', 'China ', 67)

Insert into tbcoursegrade (student, course, grade) values ('millet ', 'high number', 55)

The displayed information should be:

Student Course grade

James is 98

James English 56

*****

Red up to 89

*****

Millet high 55

Then how can it be displayed

English Language

James 98 56 77

Xiaohong ******

Xiaomi 55 null

This is a common problem: displaying rows as columns !!!

The following two methods are provided:

1. Connect yourself four times

Select yuan. student as name, gaoshu. grade as high, yingyu. grade as English, Yuwen. grade as language from tbcoursegrade as yuanleft join tbcoursegrade as gaoshu on (Yuan. student = gaoshu. student and gaoshu. course = 'high') left join tbcoursegrade as yingyuon (Yuan. student = yingyu. student and yingyu. course = 'English ') left join tbcoursegrade as yuwenon (Yuan. student = Yuwen. student and Yuwen. course = 'China ')

2. Use Case statements

Select yuan. student as name, max (case when Hou. course = 'high number' then Hou. gradeelse nullend) as high score, max (case when Hou. course = 'English 'Then Hou. gradeelse nullend) as English score, max (case when Hou. course = 'China' then Hou. grade else nullend) as language score from tbcoursegrade as Yuan join tbcoursegrade as Hou on Yuan. student = Hou. studentgroup by Yuan. student

When you look at the above two methods, you may think why left join and Max are used. If you don't understand it, you can remove left and max. From the result, you will understand.