最近正好要用到sql的交叉表

最近正好要用到sql的交叉表,
一搜,正好找到前辈01年就做好了的好东西
http://www.delphibbs.com/delphibbs/dispq.asp?lid=679161
(源作Robin_Fang)
我又在此基础上改了些东西,减少了出错和一点点增强(主要是解决了日期型字段的出错问题)

现全部贴出来共享



Create PROCEDURE CrossTable --交叉表生成器
日喀则 山南地区 那曲地区 林芝地区 拉萨市 昌都地区 阿里地区
--(Select Distinct SuppCode As SCode,MCode,PurQtyRate As Rate
--        From MtSPM A Inner Join MtSPD B On A.ID=B.MtSuppPriceM_ID
--                     Inner Join Suppliers C On A.Supp_ID=C.ID
--        Where A.Active_KID=120 And AduitPass=1) D
@vSourceTAB As Varchar(2000), --数据来源表,可以为表,视图,或者SQL语句(要用括号以及别名:如上注释段)
@vGroupbyField As Varchar(100), --被selct Group By 要显示出来的,可以多个字段(记录可以有空值)
@vTransFormCol As Varchar(50), --交叉表中的合计等函数计算值的字段
@vFunction As Varchar(100)=' Sum', --默认值,交叉表中的函数,也可以是' 2*Sum'的计算公式
@vPivotCol As Varchar(100), --要转换成列的字段,唯一列,可以是表达式'Field1+Field2'(记录可以有空值)
@vStrWhere As Varchar(1000) =Null, --Where 约束条件,可以为空
@vGroupbyFieldName As Varchar(50)=null --如果GroupField是表达式,则这里一定要指定名称,不然会出错
AS
--重要提示:@StrSql的Largest size allowed Is 8000,因此尽量将少的字段内容转换为列
Declare @StrSql As Varchar(8000) --//总的SQL语句
Declare @StrSum As Varchar(3000) --//列合计
Declare @pCols As Varchar(100)
Declare @StrWhere As Varchar(500)

if @vGroupbyFieldName is null
set @vGroupbyFieldName=@vGroupbyField

set @strsql=' Declare CursorCross Cursor For  
          Select Distinct ' + @vPivotCol + ' From ' +@vSourceTAB +' Order By ' + @vPivotCol + ' For Read only '
Print @StrSql
Execute(@StrSql)
Begin
  Set Nocount On
  Set @StrSql =''
  Set @StrSum=''
  Set @pCols=''
  IF Rtrim(Ltrim(IsNull(@vStrWhere,''))) <> ''
  Begin
    Set @StrWhere=' Where ' + @vStrWhere + ' '
  End
Else
  Set @StrWhere=''
  Open CursorCross
  While (0=0)
  Begin
    Fetch Next From CursorCross Into @pCols
    IF (@@Fetch_Status<>0) Break
    IF @pCols Is  Null --//不为空值,
    Set @pCols='Null'
    --为了防止新创建的列的标题名称,与@vGroupbyField中的字段重名,
    --新创建的列的标题名称都增加前缀[@vGroupbyField.新创建的列的标题名称]
--因Sql长度限制Max=8000,由源数据控制字段值不能为Null,因此这里不再检验值是否为Null
    Set @StrSql=@StrSql +',' + @vFunction +
        '(Case '+@vPivotCol+' When ''' + @pCols+ ''' Then '+@vTransFormCol +' Else Null End) As '+
        '['+/*Left(@vPivotCol,1)+*/@pCols+']'
--因长度限制,不计算列间之和
--    Set @StrSum=@StrSum + '+IsNull(A.' + '['+Left(@vPivotCol,1)+@pCols+']' +',0)'
  End
  Set  @StrSql = ' Select ' + @vGroupByField +' AS '+@vGroupbyFieldName+ ' ' +@StrSql + ' From ' +@vSourceTAB+ ' ' + @StrWhere +
                 ' Group By ' + @vGroupByField
  --列向合计 为字段名'TotalSum',合计方法改为重新从源表中取值,减少SQL的字符量
  Set @StrSql ='Select A.*,(select '+
               @vFunction+'(' +@vTransFormCol +') from '+ @vSourceTAB+@StrWhere+' Group by '+@vGroupbyField+ ' having '+@vGroupbyField+'=A.'+@vGroupbyFieldName
               +') As TotalSum From (' + @StrSql  +') As A'
  Set @StrSql ='Select A.* From ('+@StrSql+') As A  Order By '+@vGroupByFieldName
  Print @StrSql
  Execute(@StrSql)
  IF @@Error <>0
    Return @@Error
  Close CursorCross
  Deallocate CursorCross
  Return 0
End

go

  江苏 广西 北京 天津 浙江 西藏 云南 辽宁 香港 甘肃 山西 山东