SQL Pivot, Unpivot, and partition by usage

Demo Script

IF  not exists(SELECT 1  fromSys.sysobjectswhereName= 'Student'  andType= 'U')BEGIN    CREATE TableStudent (IDint            Identity    Primary Key, ClassIDint                        default 0,--class IDCoursenamenvarchar( -)                        ,--CoursesNamenvarchar( -)                        ,--nameScoretinyint                    default 0        --Achievements    )ENDGOIF  not exists(SELECT 1  fromStudent)begin    INSERT  intoStudent (Classid,coursename,name,score)SELECT 1,'Mathematics','Little Red', the    UNION  All    SELECT 1,'language','Little Red', -    UNION  All    SELECT 1,'English','Little Red', the    UNION  All    SELECT 1,'Mathematics','Xiao Ming', the    UNION  All    SELECT 1,'language','Xiao Ming', +    UNION  All    SELECT 1,'English','Xiao Ming', the    UNION  All    SELECT 2,'Mathematics','Xiao Qiang', the    UNION  All    SELECT 2,'language','Xiao Qiang', the    UNION  All    SELECT 2,'English','Xiao Qiang', -    UNION  All    SELECT 2,'Mathematics','Little Li', the    UNION  All    SELECT 2,'language','Little Li',94    UNION  All    SELECT 2,'English','Little Li',98    EndGO

View Code

One row to the other pivot and Unpivot

MSDN Reference

Method 1:

SELECTName,sum( CaseCoursename when 'Mathematics'  ThenScoreELSE 0 END) as 'Mathematics',sum( CaseCoursename when 'language'  ThenScoreELSE 0 END) as 'language',sum( CaseCoursename when 'English'  ThenScoreELSE 0 END) as 'English' fromStudentGroup  byName

Method 2:

SELECTT.name,sum(T. Mathematics) asMathematics,sum(T. Language) aslanguages,sum(T. English) asEnglish from (    SELECTName, Maths, Chinese, English fromStudent PIVOT (SUM(score) forCoursenameinch(Maths, Chinese, English)) TB) TGROUP  byT.name

Query Result:

Name Math Chinese English-------------------- ----------- ----------- -----------Little Red the           -           theLittle Li the          94          98Xiao Ming the           +           theXiao Qiang the           the           -

Two partition by

As per course score from high to low row

SELECTover byorderbydesc as Num  from Student

Query Result:

coursename Name score Num-------------------- -------------------- ----- --------------------Math Xiao Ming the    1Math Little Li the    2Math Little Red the    3Math Cockroach the    4English Little Li98    1English Little Red the    2English xiaoming the    3English Cockroach -    4Chinese Little Li94    1Chinese Little Red -    2Chinese xiaoming +    3Chinese Cockroach the    4

SQL Pivot, Unpivot, and partition by usage