深入交叉查询,该如何处理

深入交叉查询
select   @sql   =   @sql   +   'sum(case   Store   when   ' ' '+Store+ ' ' '

                                                    then   SaleNum   else   0   end)   as   ' ' '+Store+ ' ' ', '

    from   (select   distinct   Store   from   SaleTab)   as   a

 

select   @sql   =   left(@sql,len(@sql)-1)   +   '   from   SaleTab   group   by   Commodity '

exec(@sql)

我还想在前面加一列合计，就是该学生的成绩合计，怎样实现？
最好可以把列排序！怎样实现？
谢谢


------解决方案--------------------
set @sql= 'select sum(SaleNum) as 合计, '
select @sql = @sql + 'sum(case Store when ' ' '+Store+ ' ' '

then SaleNum else 0 end) as ' ' '+Store+ ' ' ', '

from (select distinct Store from SaleTab) as a



select @sql = left(@sql,len(@sql)-1) + ' from SaleTab group by Commodity '

exec(@sql)

------解决方案--------------------
Declare @sql Varchar(8000)
select @sql = @sql + 'sum(case Store when ' ' '+Store+ ' ' '

then SaleNum else 0 end) as ' ' '+Store+ ' ' ', '

from (select distinct Store from SaleTab) as a



select @sql = 'select SUM(Store) As 合计, ' + left(@sql,len(@sql)-1) + ' from SaleTab group by Commodity '

exec(@sql)