Horizontal table, longitudinal table conversion

1. Vertical table converted to horizontal table

Columnar SQL

CREATE TABLE [dbo].[TableA](    [Name] [nvarchar](Ten) not NULL,    [Course] [nvarchar]( -) not NULL,    [Grade] [int]  not NULL)INSERT [dbo].[TableA]([Name],[Course],[Grade])VALUES(N'Zhang San'+ I'language', the)INSERT [dbo].[TableA]([Name],[Course],[Grade])VALUES(N'Zhang San'N'Mathematics', the)INSERT [dbo].[TableA]([Name],[Course],[Grade])VALUES(N'Zhang San'N'English', -)INSERT [dbo].[TableA]([Name],[Course],[Grade])VALUES(N'John Doe'N'language', the)INSERT [dbo].[TableA]([Name],[Course],[Grade])VALUES(N'John Doe'N'Mathematics', -)

Convert SQL

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 fromTableAGroup  byName

Conversion results

2. Horizontal table converted to vertical table

Horizontal Table SQL

Create TableTable_b (IDint Identity(1,1)Primary Key, namevarchar( -), Languageint, Mathematicsint, Englishint)Insert  intoTable_b (name, language, maths, English)Values('Zhang San', -, -, the)Insert  intoTable_b (name, language, maths, English)Values('John Doe', -, -, $)Insert  intoTable_b (name, language, maths, English)Values('Harry', the, -,Panax Notoginseng)

Convert SQL

Selectname,'language'  assubjects, Languages asAchievements fromTable_bUnion  AllSelectname,'Mathematics'  asSubjects, Mathematics asAchievements fromTable_bUnion  AllSelectname,'English'  assubjects, English asAchievements fromTable_bOrder  byName, subject

Conversion results

Horizontal table, longitudinal table conversion