如何用SQL生成交叉表？

Question

要是列数可以确定，就可以这样：
select t1.supp_id ,sum(t1.301010101) as 301010101,
    sum(t1.302010101) as 302010101,
    sum(t1.306010011) as 306010011
from (
  select supp_id ,
    (case mcode when 301010101 then 1 else 0 end )*purqtyrate as 301010101,
    (case mcode when 302010101 then 1 else 0 end )*purqtyrate as 302010101,
    (case mcode when 306010011 then 1 else 0 end )*purqtyrate as 306010011
 from tablename) t1
group by t1.supp_id要是列数不定，就要做存储过程，或在客户端程序中处理。参考以前的贴子。

Answer 1

To:ZhuzhiChao
    前台是Delphi；
To:Icevi
    我的源数据Supp_ID,Code列内容是不确定的，由上一Sql提供，因此不能如你所述定死需转置的列；
    目前我正考虑在存储过程中用游标处理，尚未成功。

Answer 2

可以用存储过程来做!(以前天天都在做数据库,腻!)
MS SQL SERVER
你可以输入:
CreateCrossTAB @vSourseTAB=' SuppTAB ',@vGroupbyField=' Supp_ID ', @vTransFormCol=' PurQtyRate ',@vFunction=' sum ',@vPivotCol=' MCode '   我写了一个通用的形成交叉表的存储过程!还挺好用的!
CREATE PROCEDURE CreateCrossTAB --//交叉表查询
@vSourseTAB as varchar(50), --数据来源表,可以为表,视图,或者SQL语句(要用括号以及别名)
@vGroupbyField as varchar(50), --被selct GROUP BY 要显示出来的,可以多个字段(记录可以有空值)
@vTransFormCol as varchar(50), --交叉表中的合计等函数计算值的字段
@vFunction as varchar(50)=' sum', --默认值,交叉表中的函数,也可以是' 2*sum'的计算公式
@vPivotCol as varchar(50), --要转换成列的字段,唯一列,可以是表达式'Field1+Field2'(记录可以有空值)
@vStrWhere as varchar(500) =null --where 约束条件,可以为空
AS
declare @strsql as varchar(4000) --//总的SQL语句
declare @strSum as varchar(2000) --//列合计
declare @PCols as varchar(100)
declare @strWhere as varchar(500)
execute ('declare CursorCross Cursor for  
select distinct ' + @vPivotCol + ' from ' +@vSourseTAB +' order by ' + @vPivotCol + ' for read only ')
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.新创建的列的标题名称]
set @strsql=@strsql +',' + @vFunction +'(case isnull('+@vPivotCol+',''NULL'') when  ''' + @PCols+ ''' then '+@vTransFormCol +' else null end ) as ' 
+ '[' + @vPivotCol +'.'+ @PCols + '] ' + ' '
set @strsum=@strsum + ' +isnull(TmpCrossTAB.' + '[' + @vPivotCol +'.'+ @PCols + '] ' +',0)'
end
set  @strsql = ' select ' + @vgroupbyfield + ' ' +@strsql + ' from ' +@vSourseTAB+ ' ' + @StrWhere +
 ' group by ' + @vGroupbyField 
--列向合计 为字段名'TotalSum'
set @strsql ='select TmpCrossTAB.*, (0 ' +@strsum + ') as TotalSum from (' + @strsql  +') as TmpCrossTAB '
--print @strsql
execute(@strsql)
if @@error <>0
return @@error
close CursorCross
deallocate cursorcross
return 0
end

Answer 3

To:argin(猫眉毛)
    TKS
用你的代码作了些适当的修改已能满足要求；