PIVOT函数与UNPIVOT函数的运用

分手后的思念是犯贱 2021-12-21 05:45 191阅读 0赞

PIVOT用于将行转为列，完整语法如下：  
  
TABLE\_SOURCE  
  
PIVOT(  
  
聚合函数（value\_column）  
  
FOR pivot\_column  
  
IN(<column\_list>)  
)  
  
  
UNPIVOT用于将列转为行，完整语法如下：  
完整语法：  
TABLE\_SOURCE  
  
UNPIVOT(  
  
value\_column  
  
FOR pivot\_column  
  
IN(<column\_list>)  
)  
  
以上语法可以理解为value\_column字段与pivot\_column字段的行列（列行）转换，pivot\_column字段显示的列/行为column\_list

\--建张表Table\_A

create table Table\_A(name varchar(8),Math int,English int,Chinese int)  
insert into Table\_A values('小A',80,90,88)  
insert into Table\_A values('小B',70,90,85)  
insert into Table\_A values('小C',75,95,85)  
insert into Table\_A values('小D',80,90,75)

\--原始表数据

select \* from Table\_A

<table> 
 <tbody> 
  <tr> 
   <td>name</td> 
   <td>Math</td> 
   <td>English</td> 
   <td>Chinese</td> 
  </tr> 
  <tr> 
   <td>小A</td> 
   <td>80</td> 
   <td>90</td> 
   <td>88</td> 
  </tr> 
  <tr> 
   <td>小B</td> 
   <td>70</td> 
   <td>90</td> 
   <td>85</td> 
  </tr> 
  <tr> 
   <td>小C</td> 
   <td>75</td> 
   <td>95</td> 
   <td>85</td> 
  </tr> 
  <tr> 
   <td>小D</td> 
   <td>80</td> 
   <td>90</td> 
   <td>75</td> 
  </tr> 
 </tbody> 
</table>

\--UNPIVOT用于将列转为行

select Subject,name,Grade from Table\_A  
unpivot(Grade for Subject in (\[Math\],\[English\],\[Chinese\])) as s

<table> 
 <tbody> 
  <tr> 
   <td>Subject</td> 
   <td>name</td> 
   <td>Grade</td> 
  </tr> 
  <tr> 
   <td>Math</td> 
   <td>小A</td> 
   <td>80</td> 
  </tr> 
  <tr> 
   <td>English</td> 
   <td>小A</td> 
   <td>90</td> 
  </tr> 
  <tr> 
   <td>Chinese</td> 
   <td>小A</td> 
   <td>88</td> 
  </tr> 
  <tr> 
   <td>Math</td> 
   <td>小B</td> 
   <td>70</td> 
  </tr> 
  <tr> 
   <td>English</td> 
   <td>小B</td> 
   <td>90</td> 
  </tr> 
  <tr> 
   <td>Chinese</td> 
   <td>小B</td> 
   <td>85</td> 
  </tr> 
  <tr> 
   <td>Math</td> 
   <td>小C</td> 
   <td>75</td> 
  </tr> 
  <tr> 
   <td>English</td> 
   <td>小C</td> 
   <td>95</td> 
  </tr> 
  <tr> 
   <td>Chinese</td> 
   <td>小C</td> 
   <td>85</td> 
  </tr> 
  <tr> 
   <td>Math</td> 
   <td>小D</td> 
   <td>80</td> 
  </tr> 
  <tr> 
   <td>English</td> 
   <td>小D</td> 
   <td>90</td> 
  </tr> 
  <tr> 
   <td>Chinese</td> 
   <td>小D</td> 
   <td>75</td> 
  </tr> 
 </tbody> 
</table>

\--PIVOT函数用于将行转为列

select \* from  
(  
    select Subject,name,Grade from Table\_A  
    unpivot(Grade for Subject in (\[Math\],\[English\],\[Chinese\])) as s  
) as a  
pivot(sum(Grade) for name in (\[小A\],\[小B\],\[小C\],\[小D\])) as b

<table> 
 <tbody> 
  <tr> 
   <td>Subject</td> 
   <td>小A</td> 
   <td>小B</td> 
   <td>小C</td> 
   <td>小D</td> 
  </tr> 
  <tr> 
   <td>Chinese</td> 
   <td>88</td> 
   <td>85</td> 
   <td>85</td> 
   <td>75</td> 
  </tr> 
  <tr> 
   <td>English</td> 
   <td>90</td> 
   <td>90</td> 
   <td>95</td> 
   <td>90</td> 
  </tr> 
  <tr> 
   <td>Math</td> 
   <td>80</td> 
   <td>70</td> 
   <td>75</td> 
   <td>80</td> 
  </tr> 
 </tbody> 
</table>