sqlserver 行列互转-CSDN博客

 
 --行列互转 
 
 /****************************************************************************************************************************************************** 
 
 以学生成绩为例子，比较形象易懂 

    
 
 整理人：中国风(Roy) 

    
 
 日期:2008.06.06 
 
 ******************************************************************************************************************************************************/ 

    
 
 --1、行互列 
 
 --> --> (Roy)生成測試數據 
 
    
 
 if  
 not  
 object_id( 
 'Class' 
 )  
 is  
 null 
 
      
 drop  
 table  
 Class 
 
 Go 
 
 Create  
 table  
 Class([Student] nvarchar(2),[Course] nvarchar(2),[Score]  
 int 
 ) 
 
 Insert  
 Class 
 
 select  
 N 
 '张三' 
 ,N 
 '语文' 
 ,78  
 union  
 all 
 
 select  
 N 
 '张三' 
 ,N 
 '数学' 
 ,87  
 union  
 all 
 
 select  
 N 
 '张三' 
 ,N 
 '英语' 
 ,82  
 union  
 all 
 
 select  
 N 
 '张三' 
 ,N 
 '物理' 
 ,90  
 union  
 all 
 
 select  
 N 
 '李四' 
 ,N 
 '语文' 
 ,65  
 union  
 all 
 
 select  
 N 
 '李四' 
 ,N 
 '数学' 
 ,77  
 union  
 all 
 
 select  
 N 
 '李四' 
 ,N 
 '英语' 
 ,65  
 union  
 all 
 
 select  
 N 
 '李四' 
 ,N 
 '物理' 
 ,85  
 
 Go 
 
 --2000方法: 
 
 动态: 

    
 
 declare  
 @s nvarchar(4000) 
 
 set  
 @s= 
 '' 
 
 Select     
  @s=@s+ 
 ',' 
 +quotename([Course])+ 
 '=max(case when [Course]=' 
 +quotename([Course], 
 '' 
 '' 
 )+ 
 ' then [Score] else 0 end)' 
 
 from  
 Class  
 group  
 by 
 [Course] 
 
 exec 
 ( 
 'select [Student]' 
 +@s+ 
 ' from Class group by [Student]' 
 ) 

    

    
 
 生成静态: 

    
 
 select  
 
      
 [Student], 
 
      
 [数学]= 
 max 
 ( 
 case  
 when  
 [Course]= 
 '数学'  
 then  
 [Score]  
 else  
 0  
 end 
 ), 
 
      
 [物理]= 
 max 
 ( 
 case  
 when  
 [Course]= 
 '物理'  
 then  
 [Score]  
 else  
 0  
 end 
 ), 
 
      
 [英语]= 
 max 
 ( 
 case  
 when  
 [Course]= 
 '英语'  
 then  
 [Score]  
 else  
 0  
 end 
 ), 
 
      
 [语文]= 
 max 
 ( 
 case  
 when  
 [Course]= 
 '语文'  
 then  
 [Score]  
 else  
 0  
 end 
 )  
 
 from  
 
      
 Class  
 
 group  
 by  
 [Student] 

    
 
 GO 
 
 动态: 

    
 
 declare  
 @s nvarchar(4000) 
 
 Select     
  @s= 
 isnull 
 (@s+ 
 ',' 
 , 
 '' 
 )+quotename([Course])  
 from  
 Class  
 group  
 by 
 [Course] 
 
 exec 
 ( 
 'select * from Class pivot (max([Score]) for [Course] in(' 
 +@s+ 
 '))b' 
 ) 

    
 
 生成静态: 
 
 select  
 *  
 
 from  
 
      
 Class  
 
 pivot  
 
      
 ( 
 max 
 ([Score])  
 for  
 [Course]  
 in 
 ([数学],[物理],[英语],[语文]))b 

    
 
 生成格式: 
 
 /* 
 
 Student 数学          物理          英语          语文 
 
 ------- ----------- ----------- ----------- ----------- 
 
 李四      77          85          65          65 
 
 张三      87          90          82          78 

    
 
 (2 行受影响) 
 
 */ 

    
 
 ------------------------------------------------------------------------------------------ 
 
 go 
 
 --加上总成绩(学科平均分) 

    
 
 --2000方法: 
 
 动态: 

    
 
 declare  
 @s nvarchar(4000) 
 
 set  
 @s= 
 '' 
 
 Select     
  @s=@s+ 
 ',' 
 +quotename([Course])+ 
 '=max(case when [Course]=' 
 +quotename([Course], 
 '' 
 '' 
 )+ 
 ' then [Score] else 0 end)' 
 
 from  
 Class  
 group  
 by 
 [Course] 
 
 exec 
 ( 
 'select [Student]' 
 +@s+ 
 ',[总成绩]=sum([Score])  from Class group by [Student]' 
 ) 
 --加多一列(学科平均分用avg([Score])) 

    
 
 生成动态: 

    
 
 select  
 
      
 [Student], 
 
      
 [数学]= 
 max 
 ( 
 case  
 when  
 [Course]= 
 '数学'  
 then  
 [Score]  
 else  
 0  
 end 
 ), 
 
      
 [物理]= 
 max 
 ( 
 case  
 when  
 [Course]= 
 '物理'  
 then  
 [Score]  
 else  
 0  
 end 
 ), 
 
      
 [英语]= 
 max 
 ( 
 case  
 when  
 [Course]= 
 '英语'  
 then  
 [Score]  
 else  
 0  
 end 
 ), 
 
      
 [语文]= 
 max 
 ( 
 case  
 when  
 [Course]= 
 '语文'  
 then  
 [Score]  
 else  
 0  
 end 
 ), 
 
      
 [总成绩]= 
 sum 
 ([Score])  
 --加多一列(学科平均分用avg([Score])) 
 
 from  
 
      
 Class  
 
 group  
 by  
 [Student] 

    
 
 go 

    
 
 --2005方法: 

    
 
 动态: 

    
 
 declare  
 @s nvarchar(4000) 
 
 Select     
  @s= 
 isnull 
 (@s+ 
 ',' 
 , 
 '' 
 )+quotename([Course])  
 from  
 Class  
 group  
 by 
 [Course]  
 --isnull(@s+',','') 去掉字符串@s中第一个逗号 
 
 exec 
 ( 
 'select [Student],' 
 +@s+ 
 ',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a  
 
 pivot (max([Score]) for [Course] in(' 
 +@s+ 
 '))b ' 
 ) 

    
 
 生成静态: 

    
 
 select  
 
      
 [Student],[数学],[物理],[英语],[语文],[总成绩]  
 
 from  
 
      
 ( 
 select  
 *,[总成绩]= 
 sum 
 ([Score])over(partition  
 by  
 [Student])  
 from  
 Class) a  
 --平均分时用avg([Score]) 
 
 pivot  
 
      
 ( 
 max 
 ([Score])  
 for  
 [Course]  
 in 
 ([数学],[物理],[英语],[语文]))b  

    
 
 生成格式: 

    
 
 /* 
 
 Student 数学          物理          英语          语文          总成绩 
 
 ------- ----------- ----------- ----------- ----------- ----------- 
 
 李四      77          85          65          65          292 
 
 张三      87          90          82          78          337 

    
 
 (2 行受影响) 
 
 */ 

    
 
 go 

    
 
 --2、列转行 
 
 --> --> (Roy)生成測試數據 
 
    
 
 if  
 not  
 object_id( 
 'Class' 
 )  
 is  
 null 
 
      
 drop  
 table  
 Class 
 
 Go 
 
 Create  
 table  
 Class([Student] nvarchar(2),[数学]  
 int 
 ,[物理]  
 int 
 ,[英语]  
 int 
 ,[语文]  
 int 
 ) 
 
 Insert  
 Class 
 
 select  
 N 
 '李四' 
 ,77,85,65,65  
 union  
 all 
 
 select  
 N 
 '张三' 
 ,87,90,82,78 
 
 Go 

    
 
 --2000: 

    
 
 动态: 

    
 
 declare  
 @s nvarchar(4000) 
 
 select  
 @s= 
 isnull 
 (@s+ 
 ' union all ' 
 , 
 '' 
 )+ 
 'select [Student],[Course]=' 
 +quotename( 
 Name 
 , 
 '' 
 '' 
 ) 
 --isnull(@s+' union all ','') 去掉字符串@s中第一个union all 
 
 + 
 ',[Score]=' 
 +quotename( 
 Name 
 )+ 
 ' from Class' 
 
 from  
 syscolumns  
 where  
 ID=object_id( 
 'Class' 
 )  
 and  
 Name  
 not  
 in 
 ( 
 'Student' 
 ) 
 --排除不转换的列 
 
 order  
 by  
 Colid 
 
 exec 
 ( 
 'select * from (' 
 +@s+ 
 ')t order by [Student],[Course]' 
 ) 
 --增加一个排序 

    
 
 生成静态: 
 
 select  
 *  
 
 from  
 ( 
 select  
 [Student],[Course]= 
 '数学' 
 ,[Score]=[数学]  
 from  
 Class  
 union  
 all  
 
 select  
 [Student],[Course]= 
 '物理' 
 ,[Score]=[物理]  
 from  
 Class  
 union  
 all  
 
 select  
 [Student],[Course]= 
 '英语' 
 ,[Score]=[英语]  
 from  
 Class  
 union  
 all  
 
 select  
 [Student],[Course]= 
 '语文' 
 ,[Score]=[语文]  
 from  
 Class)t  
 
 order  
 by  
 [Student],[Course] 

    
 
 go 
 
 --2005: 

    
 
 动态: 

    
 
 declare  
 @s nvarchar(4000) 
 
 select  
 @s= 
 isnull 
 (@s+ 
 ',' 
 , 
 '' 
 )+quotename( 
 Name 
 ) 
 
 from  
 syscolumns  
 where  
 ID=object_id( 
 'Class' 
 )  
 and  
 Name  
 not  
 in 
 ( 
 'Student' 
 )  
 
 order  
 by  
 Colid 
 
 exec 
 ( 
 'select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in(' 
 +@s+ 
 '))b' 
 ) 

    
 
 go 
 
 select  
 
      
 Student,[Course],[Score]  
 
 from  
 
      
 Class  
 
 unpivot  
 
      
 ([Score]  
 for  
 [Course]  
 in 
 ([数学],[物理],[英语],[语文]))b 

    
 
 生成格式: 
 
 /* 
 
 Student Course Score 
 
 ------- ------- ----------- 
 
 李四      数学      77 
 
 李四      物理      85 
 
 李四      英语      65 
 
 李四      语文      65 
 
 张三      数学      87 
 
 张三      物理      90 
 
 张三      英语      82 
 
 张三      语文      78 

    
 
 (8 行受影响) 
 
 */ 