longitudinal table to Horizontal table
Create TableScore (Namenvarchar( -), Classnvarchar( -), scoreint)Insert intoScore (Name, Class, score)Values('Zhang San','language',98)Insert intoScoreValues('Zhang San','language',98)Insert intoScoreValues('Zhang San','Mathematics', the)Insert intoScoreValues('Zhang San','Physical', +)Insert intoScoreValues('John Doe','language', -)Insert intoScoreValues('John Doe','Mathematics', the)Insert intoScoreValues('John Doe','Physical', -)Select * fromscoreSelectT.name,SUM( CaseT.class when 'language' ThenT.scoreElse 0 End) aslanguages,SUM( CaseT.class when 'Mathematics' ThenT.scoreElse 0 End) asMathematics,SUM( CaseT.class when 'Physical' ThenT.scoreElse 0 End) asPhysics,SUM( CaseT.class when 'Foreign Languages' ThenT.scoreElse 0 End) asForeign Language,SUM( CaseT.class when 'political' ThenT.scoreElse 0 End) asPolitics,SUM( CaseT.class when 'Sports' ThenT.scoreElse 0 End) asSports fromScore asTGroup byT.name
Horizontal Table to longitudinal table
--converted tables Insert a new tableSelectT.name,SUM( CaseT.class when 'language' ThenT.scoreElse 0 End) aslanguages,SUM( CaseT.class when 'Mathematics' ThenT.scoreElse 0 End) asMathematics,SUM( CaseT.class when 'Physical' ThenT.scoreElse 0 End) asPhysical intoSCOREHB fromScore asTGroup byT.nameSelect * fromdbo. SCOREHB--UNION ALL link 3 accountsSelectT.name,'language' asclass,t. Language asscore fromScorehb asTUnion AllSelectT.name,'Mathematics' asclass,t. Mathematics asscore fromScorehb asTUnion AllSelectT.name,'Physical' asclass,t. Physics asscore fromScorehb asTOrder byT.namedesc
Pivot longitudinal table to horizontal table
Select T2. Name, T2. Mathematics, T2, T2. language from as t1pivot (sum for inch as T2
Unpivot horizontal table to longitudinal table
Select * from for in as T4
SQL Server Crossbar