MSSQLServer longitudinal table to transverse table transverse table to longitudinal table row to column career change

MSSQLServer longitudinal table to transverse table transverse table to longitudinal table

To create a table statement and insert a data statement:

CREATE TABLEtest_y ([Name] [nchar](Ten)NULL,    [Course] [nchar](Ten)NULL,    [Grade] [int] NULL) Insert  intoTest_yValues('Zhang San','language', the);Insert  intoTest_yValues('Zhang San','Mathematics', the);Insert  intoTest_yValues('Zhang San','English', -);Insert  intoTest_yValues('John Doe','language', -);Insert  intoTest_yValues('John Doe','Mathematics', -);Insert  intoTest_yValues('John Doe','English', the);CREATE TABLEtest_x ([Name] [nchar](Ten)NULL,    [language] [nchar](Ten)NULL,    [Mathematics] [nchar](Ten)NULL,    [English] [nchar](Ten)NULL)Insert  intoTest_xValues('Zhang San', the, the, -);Insert  intoTest_xValues('John Doe', -, -, the);

longitudinal table to horizontal table effect show:

The vertical table to horizontal table SQL statement is as follows:

Method One:

Select *  fromtest_y;SelectName,sum( CaseCourse when 'language'  ThenGradeElse 0 End) aslanguages,sum( CaseCourse when 'Mathematics'  ThenGradeElse 0 End) asMathematics,sum( CaseCourse when 'English'  ThenGradeElse 0 End) asEnglish fromTest_yGroup  byName;

Method Two:

Select * from test_y    to  Course in ([Language],[Math],[English]) as    test_x;

Horizontal table to longitudinal table effect show:

Horizontal table to longitudinal table The SQL statement is as follows:

Method One:

Select *  fromtest_x;SelectName,'language'  asCourse, Chinese asGrade fromTest_xUnion  AllSelectName,'Mathematics'  asCourse, Math asGrade fromTest_xUnion  AllSelectName,'English'  asCourse, English asGrade fromTest_xOrder  byNamedesc;

Method Two:

Select  from  for In ([Mathematics],[English],[language]) as test_y

Finally thanks @ Mobei Otter tips.

Novice learning, master neglect can be;

. NET Technology Exchange Group:70895254