SQL Server row and column interchange implementation idea (aggregate function) _mssql

Sometimes you run into the needs of a row-turn column (that is, the column's value as a column name), and I usually do it with the case end + aggregate function.

As follows:

Declare @t table (studentname nvarchar, Subject nvarchar (), Score int) Insert into @t (Studentname,subject,scor  
(e) VALUES (' Student a ', ' Chinese ', 80);  
Insert into @t (studentname,subject,score) VALUES (' Student a ', ' math ', 78);  
Insert into @t (studentname,subject,score) VALUES (' Student a ', ' English ', 92);  
Insert into @t (studentname,subject,score) VALUES (' Student B ', ' Chinese ', 89);  
Insert into @t (studentname,subject,score) VALUES (' Student B ', ' math ', 87);  
Insert into @t (studentname,subject,score) VALUES (' Student B ', ' English ', 75);  
Insert into @t (studentname,subject,score) VALUES (' Student C ', ' Chinese ', 92);  
Insert into @t (studentname,subject,score) VALUES (' Student C ', ' math ', 74);  
Insert into @t (studentname,subject,score) VALUES (' Student C ', ' English ', 65);  
Insert into @t (studentname,subject,score) VALUES (' Student d ', ' Chinese ', 79);  
Insert into @t (studentname,subject,score) VALUES (' Student d ', ' math ', 83);  
Insert into @t (studentname,subject,score) VALUES (' Student d ', ' English ', 81); Insert into @t (Studentname,subject,score) VALUES (' Student e ', ' Chinese ', 73);  
Insert into @t (studentname,subject,score) VALUES (' Student e ', ' math ', 84);  
Insert into @t (studentname,subject,score) VALUES (' Student e ', ' English ', 93);  
Insert into @t (studentname,subject,score) VALUES (' Student f ', ' Chinese ', 79);  
Insert into @t (studentname,subject,score) VALUES (' Student f ', ' math ', 86); 
 
Insert into @t (studentname,subject,score) VALUES (' Student f ', ' English ', 84); Select Studentname, sum (case when Subject = N ' Chinese ' then Score else 0) Chinese, sum (case when Subject = n ' math ' th 
 En Score Else 0 end) of Math, sum (case when Subject = N ' English ' then Score else 0 end) Engilsh to @t GROUP by Studentname

Today I see a new writing, pivot can achieve the same function (2005 to start support).

The syntax for pivot is:

Table_source

Pivot (aggregate function (Value_column) pivot_column for (columnlist))

A little explanation:

Table_source: is the table we want to convert. pivot_column: is the name of the column to be row-column. Value_column: is the value of the converted column. Columnlist is the column to be generated.

Also the above example, using pivot can write the same result:

Select Studentname,
    [Chinese] Chinese, [
    math] Math,
    [English] 中文版
 from
(SELECT * from @t) T1
Pivot (SUM (Score) for Subject in ([Chinese],[English],[mathematics]) T2

The corresponding Unpivot is the column change (column name as value),

The syntax for Unpivot is:

Table_source

Unpivot (Value_column ubpivot_column for (columnlist))

The meaning of the parameters is the same as that of pivot. Here we can simply turn the back of the new, so we get the original table:

Select Studentname,
    Subject,
    Score
 from
(SELECT * to @t) T1
pivot (sum (Score) for Subject in ([Chinese] , [English],[math]) T2
Unpivot (Score for Subject in ([Chinese],[english],[math]) t3

The above is the entire content of this article, I hope that you learn to realize the ranks of SQL Server to help each other.