Sql纵向表转为横向表，并分组统计(转)

 在写系统尤其是在写课程系统是经常会遇到类似如下纵-横转换，在使用group by ... with cube/rollup + grouping() 来实现是非常容易做到的。

 

1. 用于测试的数据如下：

declare   @tab   table (Class  varchar ( 20 ),Student  varchar ( 20 ),Course  varchar ( 50 ),Grades  decimal ( 7 , 2 ));
insert   into   @tab (Class,Student,Course,Grades)  values ( ' A班 ' , ' 张三 ' , ' 语文 ' , 60 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' A班 ' , ' 张三 ' , ' 数学 ' , 70 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' A班 ' , ' 张三 ' , ' 英语 ' , 80 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' A班 ' , ' 李四 ' , ' 语文 ' , 30 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' A班 ' , ' 李四 ' , ' 数学 ' , 40 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' A班 ' , ' 李四 ' , ' 英语 ' , 50 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' B班 ' , ' 王五 ' , ' 语文 ' , 65 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' B班 ' , ' 王五 ' , ' 数学 ' , 75 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' B班 ' , ' 王五 ' , ' 英语 ' , 85 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' B班 ' , ' 赵六 ' , ' 语文 ' , 35 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' B班 ' , ' 赵六 ' , ' 数学 ' , 45 );
insert   into   @tab (Class,Student,Course,Grades)  values ( ' B班 ' , ' 赵六 ' , ' 英语 ' , 55 );

select   *   from   @tab

 

 

2. 实现此效果的SQL语句如下：

select  
    ( case   when   Grouping (Class) = 1   then   ' 总平均 '   when   Grouping (Student) = 1   then   ''   else  Class  end  )  as  Class
    ,( case   when   Grouping (Class) = 1   then   ''   when   Grouping (Student) = 1   then   ' 平均 '   else  Student  end )  as  Student
    , cast ( avg (语文)  as   decimal ( 7 , 2 ))  as  语文
    , cast ( avg (数学)  as   decimal ( 7 , 2 ))  as  数学
    , cast ( avg (英语)  as   decimal ( 7 , 2 ))  as  英语 
    , cast ( avg (总分)  as   decimal ( 7 , 2 ))  as  总分
from  (
     select  Class,Student
    ,( select   isnull ( sum (Grades), 0 )  from   @tab   where  Class = t.Class  and  Student = t.Student  and  Course = ' 语文 ' )  as   ' 语文 '
    ,( select   isnull ( sum (Grades), 0 )  from   @tab   where  Class = t.Class  and  Student = t.Student  and  Course = ' 数学 ' )  as   ' 数学 '
    ,( select   isnull ( sum (Grades), 0 )  from   @tab   where  Class = t.Class  and  Student = t.Student  and  Course = ' 英语 ' )  as   ' 英语 '
    ,( select   isnull ( sum (Grades), 0 )  from   @tab   where  Class = t.Class  and  Student = t.Student)  as   ' 总分 '
     from   @tab   as  t
     group   by  Class,Student
    )  as  tempTab
group   by  Class,Student,语文,数学,英语,总分  with  rollup
having   Grouping (语文) = 1  
     and   Grouping (数学) = 1
     and   Grouping (英语) = 1

 

 

这里没有考虑做到通用，如果做到通用可能会比较复杂，也不知道性能会怎么样。

转载于:https://www.cnblogs.com/tonykan/archive/2013/05/27/3102716.html