SQL Server 行列转换示例

SQL Server的行列转换功能非常实用，但是由于其语法不好懂，使很多初学者都不愿意使用它。下面我就用示例的形式，逐一展现Pivot和UnPivot的魅力。如下图

 由于近期CSDN的图片服务器不稳定，如果图片挂了，请读者访问图片原地址：

http://img242.ph.126.net/4isS79TpI8jxQViyLD410Q==/2136958023189057751.jpg

1.从Wide Table of Months 转换到 Narrow Table的示例

  
  

   
   

    
    

     
     [c-sharp] 
     
     view plain
     
     copy
     
     print
     
     ?
    
    
   
   
   
   
select [Year],[Month],[Sales] from  
(  
    select * from MonthsTable  
)p  
unpivot  
(  
    [Sales] for [Year] in ([2001],[2002],[2003])  
)t  
order by [Year]  
  
  
select [Year],[Month],[Sales] from
(
	select * from MonthsTable
)p
unpivot
(
	[Sales] for [Year] in ([2001],[2002],[2003])
)t
order by [Year] 

2.从Narrow Table 转换到 Wide Table of Years的示例

  
  

   
   

    
    

     
     [c-sharp] 
     
     view plain
     
     copy
     
     print
     
     ?
    
    
   
   
   
   
select * from  
(  
    select * from NarrowTable  
)p  
pivot  
(  
    Sum(Sales) for [Month] in ([Jan],[Feb],[Mar])  
)t  
  
  
select * from
(
	select * from NarrowTable
)p
pivot
(
	Sum(Sales) for [Month] in ([Jan],[Feb],[Mar])
)t  

3.从Wide Table of Months 转换到 Wide Table of Years的示例

  
  

   
   

    
    

     
     [c-sharp] 
     
     view plain
     
     copy
     
     print
     
     ?
    
    
   
   
   
   
with d as  
(  
select [Year],[Month],[Sales] from  
(  
    select * from MonthsTable  
)p  
unpivot  
(  
    [Sales] for [Year] in ([2001],[2002],[2003])  
)t  
)  
select * from   
(  
    select * from d  
)p  
pivot  
(  
    Sum(Sales) for [Month] in ([Jan],[Feb],[Mar])  
)t  
  
  
with d as
(
select [Year],[Month],[Sales] from
(
	select * from MonthsTable
)p
unpivot
(
	[Sales] for [Year] in ([2001],[2002],[2003])
)t
)
select * from 
(
	select * from d
)p
pivot
(
	Sum(Sales) for [Month] in ([Jan],[Feb],[Mar])
)t  

4.从Wide Table of Years 转换到 Narrow Table的示例

[c-sharp] view plain copy print ?

1. select [Year],[Month],[Sales] from  
2. (  
3.     select * from YearTable  
4. )p  
5. unpivot  
6. (  
7.     Sales for [Month] in ([Jan],[Feb],[Mar])  
8. )t  

select [Year],[Month],[Sales] from ( select * from YearTable )p unpivot ( Sales for [Month] in ([Jan],[Feb],[Mar]) )t  

5.从Narrow Table 转换到 Wide Table of Month的示例

  
  

   
   

    
    

     
     [c-sharp] 
     
     view plain
     
     copy
     
     print
     
     ?
    
    
   
   
   
   
select * from  
(  
    select * from NarrowTable  
)p  
pivot  
(  
    Sum(Sales) for [Year] in ([2001],[2002],[2003])  
)t  
  
  
select * from
(
	select * from NarrowTable
)p
pivot
(
	Sum(Sales) for [Year] in ([2001],[2002],[2003])
)t  

6.从Wide Table of Years 转换到 Wide Table of Month的示例

[c-sharp] view plain copy print ?

1. with d as  
2. (  
3. select [Year],[Month],[Sales] from  
4. (  
5.     select * from YearTable  
6. )p  
7. unpivot  
8. (  
9.     Sales for [Month] in ([Jan],[Feb],[Mar])  
10. )t  
11. )  
12. select * from  
13. (  
14.     select * from d  
15. )p  
16. pivot  
17. (  
18.     Sum(Sales) for [Year] in ([2001],[2002],[2003])  
19. )t  

with d as ( select [Year],[Month],[Sales] from ( select * from YearTable )p unpivot ( Sales for [Month] in ([Jan],[Feb],[Mar]) )t ) select * from ( select * from d )p pivot ( Sum(Sales) for [Year] in ([2001],[2002],[2003]) )t