有时候需要将结果旋转以便在水平方向显示列,水平方向显示行,即所谓的交叉表(PrvotTable)。在SQL显示它也比较的简单:
1:结果确定的交叉表:
       Year      Quarter      Amount(表Prvot)
       ----          -------           ------
       1990         1               1.1                    
       1990         2               1.2          显示成:   Year          Q1            Q2                 Q3                Q4
       1990         3               1.3                            -----           ---             ----                 ---                 ---
       1990         4               1.4                          1990            1.1            1.2               1.3               1.4   
因为Quarter是固定的,姑且称其为结果确定的交叉表吧。实现的方法如下:

SELECT Year, 
            SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
            SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
            SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
            SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
       FROM Prvot


       GROUP BY Year(具体参考SQL的帮助,搜索交叉数据表即可)
2:结果不确定的交叉表:
     假如Quarter是动态的变动的,那么就不能简单的使用上述的case...when了,可以构造动态的SQL语句,来实现上述的SUM语  句。

declare @goodscode varchar(20)
     declare @goodsname varchar(40)
     declare @str varchar(2000)     set @str = ''
     declare goods_cur cursor for
          select goodscode,goodsname
          from pub_goods
         order by goodscode    open goods_cur
    fetch next from goods_cur into @goodscode,@goodsname
    while @@fetch_status = 0
      begin
         set @str = @str + 'sum(case goodscode when ''' + @goodscode + ''' then targetqty else 0 end) as ''' + @goodsname + ''','
         fetch next from goods_cur into @goodscode,@goodsname
      end
   close goods_cur
   deallocate goods_cur
   set @str = substring(@str,1,len(@str)-1)
   exec('select districtcode,' + @str + ' from ( select distinct districtcode,goodscode,targetqty  from report_hospital_use   where   
             districtcode is not null   ) as t group by districtcode')



3:现在的问题是,能不能不使用游标来实现上述的SUM语句的构造过程。