An Implementation Method for SQL Server row-to-column Conversion

For some businesses, the storage of data in the table is exactly the same as the final result of reversing the source table. At this time, we encountered the problem of converting rows into columns, to simplify the problem, you do not have to worry about the table design and SQL statements as follows:

The source table is as follows: (The table name is info)

 

------------------------------------------------------------------------

SelectUsername,   

Language = (CaseCourseWhen 'China' ThenGradeElse0End),   

Mathematics = (CaseCourseWhen 'Mat' ThenGradeElse0End),  

Politics = (CaseCourseWhen 'Politics' ThenGradeElse0End)

FromInfo

Let's look at our execution results:

After seeing this, we all know what we should do, group, group by name, and sum the three courses separately;

------------------------------------------------------------------------

Target results:

 

The SQL statement is:

Select username,   Language = sum (Case course when 'China' then grade else 0 end ),   Math = sum (Case course when 'mate' then grade else 0 end ),   Politics = sum (Case course when 'state' then grade else 0 end) from Info group by username

SelectUsername,   

Language =Sum(CaseCourseWhen 'China' ThenGradeElse0End),  

Mathematics =Sum(CaseCourseWhen 'Mat' ThenGradeElse0End), 

Politics =Sum(CaseCourseWhen 'Politics' ThenGradeElse0End)

FromInfo

GroupUsername