自分定分组交叉透视存储过程

IF EXISTS (SELECT name 
  FROM   sysobjects 
  WHERE  name = N'p_pubcrosstable' 
  AND  type = 'P')
    DROP PROCEDURE p_pubcrosstable
GO


CREATE PROCEDURE p_pubcrosstable
( 
  @sourcesql varchar(8000), -- 需要交叉的sql
  @sourcetable varchar(128), --需要交叉的table
  @groupbyfields varchar(500), --分组字段
  @colfields     varchar(500), --列字段 可以多字段
  @valuefields   varchar(500), --统计值字段可以多字段
  @isrowtotal    bit = 1, --行统计
  @issumtotal    bit = 1, --列统计 暂不支持
  @tag           varchar(1000) = '' --<concatfieldname>xxx</concatfieldname> 分组合并字段 <iscount>1</iscount>计次 <insertintotablename>##abc</insertintotablename> 将结果保存到表名
)
AS
begin
  
  /*
   tsp 原创 支持SQL2000，


    <concatfieldname>xxx</concatfieldname> 分组合并字段
    部门   人数   姓名xxx
    开发部  2    唐工;马工;


    当列字段为多个时
     
    部门   7月|期初人数  7月|新进人数 7月|离职人数  7月|初末人数
    sunq    9                2            1             8


  */
  declare @errormsg varchar(255), @iscount bit, @concatfieldname varchar(128)


  set @groupbyfields = isnull(@groupbyfields, '')
  set @colfields = isnull(@colfields, '')
  set @valuefields = isnull(@valuefields, '')
  set @groupbyfields = rtrim(ltrim(@groupbyfields))
  set @colfields = rtrim(ltrim(@colfields))
  set @valuefields = rtrim(ltrim(@valuefields))


  set @iscount = 0
  set @concatfieldname = ''
  if @tag <> '' and dbo.f_analysestr(@tag, 'concatfieldname') <> '' 
  begin
    set @concatfieldname = dbo.f_analysestr(@tag, 'concatfieldname')
  end


  if @tag <> '' and dbo.f_analysestr(@tag, 'iscount') = '1' 
  begin
    set @iscount = 1
  end


  if @groupbyfields = '' or @colfields = '' or @valuefields = ''
  begin
    select @errormsg = dbo.f_geterror('99996') --99996 参数不正确
    raiserror(@errormsg, 16, 1) with nowait
    return -1  
  end


  set @groupbyfields = replace(@groupbyfields, ';', ',')
  set @colfields = replace(@colfields, ';', ',')
  set @valuefields = replace(@valuefields, ';', ',') 
  if left(@groupbyfields, 1) = ',' set @groupbyfields = right(@groupbyfields, len(@groupbyfields) - 1) 
  if left(@colfields, 1) = ',' set @colfields = right(@colfields, len(@colfields) - 1) 
  if left(@valuefields, 1) = ',' set @valuefields = right(@valuefields, len(@valuefields) - 1) 
  if right(@groupbyfields, 1) = ',' set @groupbyfields = left(@groupbyfields, len(@groupbyfields) - 1) 
  if right(@colfields, 1) = ',' set @colfields = left(@colfields, len(@colfields) - 1) 
  if right(@valuefields, 1) = ',' set @valuefields = left(@valuefields, len(@valuefields) - 1) 


  declare @spid varchar(30), @sql varchar(8000), @nsql nvarchar(4000)
  declare @rowcount int, @recordcount int, @recordcount2 int, @groupcount int, @colfieldcount int


  set @spid = convert(varchar, @@spid)


  declare @insertintotablename varchar(128), @sourcetemptablename varchar(128)
  declare @distinctcoltablename varchar(128), @tablename_concat varchar(128)
  declare @tablename_temp varchar(128)


  set @insertintotablename = ''
  set @sourcetemptablename = ''
  set @tablename_concat = ''


  set @sourcetemptablename = '##t_crosssource_sunq_' +  @spid
  --set @insertintotablename = '##t_crossresult_sunq_' +  @spid
  set @distinctcoltablename = '##t_crossdistinctcol_sunq_' +  @spid
  set @tablename_concat = '##t_crossconcat_sunq_' +  @spid


  if dbo.f_analysestr(@tag, 'insertintotablename') <> '' set @insertintotablename = dbo.f_analysestr(@tag, 'insertintotablename')


  if exists (select * from tempdb.dbo.sysobjects where name = @sourcetemptablename) 
  begin
    exec('drop table '+ @sourcetemptablename)  
  end


  if exists (select * from tempdb.dbo.sysobjects where name = @distinctcoltablename) 
  begin
    exec('drop table '+ @distinctcoltablename)  
  end


  set @nsql = ' create table ' + @distinctcoltablename + 
    '(
     colno int identity(1,1),
     colvalue varchar(128)
     ) '
  exec(@nsql)
  if @@error <> 0 goto error


   --生成源数据
  if @sourcesql <> ''
  begin
    set @sql =  'select * into ' +@sourcetemptablename + ' from (' +  @sourcesql + ') a'
    exec(@sql)
    if @@error <> '' goto error  
  end
  else if @sourcetable <> ''
  begin
    set @sql =  'select * into ' +@sourcetemptablename + ' from ' + @sourcetable 
    exec(@sql)
    if @@error <> '' goto error      
  end


  declare @t_col_fields table
  (
   fieldno  int,
   fieldname varchar(128)
  )


  declare @t_value_fields table
  (
   fieldno  int,
   fieldname varchar(128)
  )
      
  --生成列字段
  declare @i int, @p int, @no int, @gno int
  declare @fieldname varchar(128), @fieldtype varchar(128)
  declare @s varchar(8000), @strtemp varchar(3000)
  declare @s1 varchar(8000), @s2 varchar(8000), @s3 varchar(8000)




  set @strtemp = @colfields + ',' 
  set @p = charindex(',' , @strtemp)
  set @i = 0
  while  @p > 0
  begin
    set @fieldname = substring(@strtemp, 1, @p - 1)
    set @i = @i + 1
    insert into @t_col_fields(fieldno, fieldname) values(@i,  @fieldname)


    select @fieldtype = c.[name]
    from tempdb.dbo.syscolumns a inner join tempdb.dbo.sysobjects  b
         on a.id = b.id inner join tempdb.dbo.systypes c
              on a.xtype = c.xtype
    where b.xtype = 'u' and b.name = @sourcetemptablename and a.name = @fieldname
 
    set @nsql = ''
    if @fieldtype in ('char', 'nchar', 'nvarchar', 'varchar')
    begin
      set @nsql = ' update ' + @sourcetemptablename + ' set ' +  @fieldname  + '='''''+
                  ' where ' +  @fieldname  + ' is null '
    end
    else if @fieldtype in ('bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'real')
    begin
      set @nsql = ' update ' + @sourcetemptablename + ' set ' +  @fieldname  + '=0'+
                  ' where ' +  @fieldname  + ' is null '
    end
    else if @fieldtype in ('datetime', 'smalldatetime')
    begin
      set @nsql = ' update ' + @sourcetemptablename + ' set ' +  @fieldname  + '=0'+
                  ' where ' +  @fieldname  + ' is null '
    end


    if @nsql <> '' exec sp_executesql @nsql
 
    set @strtemp = substring(@strtemp, @p + 1,  len(@strtemp) - @p)
    set @p = charindex(',' , @strtemp)
  end 


  set @strtemp = @valuefields + ',' 
  set @p = charindex(',' , @strtemp)
  set @i = 0
  while  @p > 0
  begin
    set @fieldname = substring(@strtemp, 1, @p - 1)
    set @i = @i + 1
    insert into @t_value_fields(fieldno, fieldname) values(@i,  @fieldname)
    
    set @strtemp = substring(@strtemp, @p + 1,  len(@strtemp) - @p)
    set @p = charindex(',' , @strtemp)
  end 


  declare @t_sumcase_fields table
  (
   sqlno  int,
   sqlbody varchar(8000)
  )


  declare @t_createconcat_fields table
  (
   sqlno  int,
   sqlbody varchar(8000)
  )


  declare @t_selectconcat_fields table
  (
   sqlno  int,
   sqlbody_declare varchar(2000),
   sqlbody_concat  varchar(7000)
  )


  declare @t_updateconcat_fields table
  (
   sqlno  int,
   sqlbody varchar(8000)
  )


  select @colfieldcount = count(*) from @t_col_fields


  if charindex('period', @colfields) > 0 and @colfieldcount = 1
  begin
 set @strtemp = @colfields
 set @strtemp = replace(@strtemp, ',', '')
 --set @strtemp = 'convert(varchar,' + @strtemp + ')'

 set @nsql = ' insert into ' + @distinctcoltablename + '(colvalue) 
               select convert(varchar, colvalue)
               from (select distinct ' + @strtemp + ' as colvalue  from ' + @sourcetemptablename  +  ') a ' + 
             ' order by colvalue '
  end
  else
  begin
 set @strtemp = @colfields
 set @strtemp = replace(@strtemp, ',', ')+''|''+convert(varchar,')
 set @strtemp = 'convert(varchar,' + @strtemp + ')'

 set @nsql = ' insert into ' + @distinctcoltablename + '(colvalue) 
               select colvalue 
               from (select distinct ' + @strtemp + ' as colvalue  from ' + @sourcetemptablename  +  ') a ' + 
             ' order by colvalue ' 
  end


  --print @nsql
  exec(@nsql)
  if @@error <> 0 goto error


  select @recordcount = count(*) from @t_value_fields
  select @recordcount2 = 0


  set @nsql = 'set @value = 0
               select  @value = count(*) from ' + @distinctcoltablename 
  exec sp_executesql @nsql, N'@value int output', @value = @recordcount2 output 


  if @recordcount2 >= 1024 
  begin
    select @errormsg = dbo.f_geterror('99996') --99996 参数不正确。
    select @errormsg = @errormsg + ' field count >= 1024'
    raiserror(@errormsg, 16, 1) with nowait
    return -1
  end


  /*
  exec(' select * from ' + @distinctcoltablename)
  */


  --select @recordcount2
  declare @bgno int, @edno int, @totalfieldname varchar(30)
  declare @outcrossfields varchar(2000), @outcrossfields_detail varchar(2000)
  set @no = 0
  set @i = 0


  set @outcrossfields = ''
  set @outcrossfields_detail = ''
  set @strtemp = @colfields
  set @strtemp = replace(@strtemp, ',', ')+''''|''''+convert(varchar,')
  set @strtemp = 'convert(varchar,' + @strtemp + ')'


  select top 1 @i = fieldno, @fieldname = fieldname
  from @t_value_fields
  where fieldno > @i 
  order by fieldno
  set @rowcount = @@rowcount
  while @rowcount = 1
  begin
    --select @s = @s + ', sum(case convert(varchar,sex) when ''' + convert(varchar, colvalue) + ''' then emp_id else 0 end) as [' + case when convert(varchar, colvalue) = '' then 'null' else convert(varchar, colvalue) end + ']' from ##t_crossdistinctcol_sunq_64 where colno <= 60  
    --, sum(case convert(varchar,sex) when '' then emp_id else 0 end) as [null], sum(case convert(varchar,sex) when '男' then emp_id else 0 end) as [男], sum(case convert(varchar,sex) when '女' then emp_id else 0 end) as [女] + '_emp_id]'
    set @totalfieldname = @fieldname


    if @iscount = 1 
      set @totalfieldname = '1'
    else
    begin
   select @fieldtype = c.[name]
   from tempdb.dbo.syscolumns a inner join tempdb.dbo.sysobjects  b
        on a.id = b.id inner join tempdb.dbo.systypes c
        on a.xtype = c.xtype
   where b.xtype = 'u' and b.name = @sourcetemptablename and a.name = @fieldname
   if @fieldtype in ('char', 'nchar', 'nvarchar', 'varchar')  --字符型字段只计次
   begin
     set @totalfieldname = '1'
   end
    end


 if @recordcount = 1
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + '']''' + 
               ' from '+  @distinctcoltablename + 
               ' order by colno '   
      --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @outcrossfields output
 end
 else
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + ''_' + @fieldname + ']''' + 
               ' from '+  @distinctcoltablename + 
               ' order by colno  '
   --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @outcrossfields output     
 end


 if @recordcount = 1
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + ''_detailid]''' + 
               ' from '+  @distinctcoltablename + 
               ' order by colno '   
      --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @outcrossfields_detail output
 end
 else
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + ''_' + @fieldname + '_detailid]''' + 
               ' from '+  @distinctcoltablename + 
               ' order by colno  '
   --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @outcrossfields_detail output     
 end


 set @bgno = 1
    while @bgno <= @recordcount2
    begin
      set @edno = @bgno + 30
 if @recordcount = 1
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '',sum(case ' +  @strtemp + ' when '''''' + convert(varchar, colvalue) + '''''' then ' + 
                                              @totalfieldname + ' else 0 end) as ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + '']''' + 
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
        --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output
 end
 else
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '',sum(case ' +  @strtemp + ' when '''''' + convert(varchar, colvalue) + '''''' then ' +
                                              @totalfieldname + ' else 0 end) as ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + ''_' + @fieldname + ']''' + 
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
   --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output     
 end
 --print @s

 set @no = @no + 1
 insert into @t_sumcase_fields(sqlno, sqlbody) values(@no, @s)


      if @concatfieldname <> '' --合并字符串字段
      begin
        --create
      if @recordcount = 1
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', convert(varchar(1000), space(0)) as ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + ''_detailid]''' + 
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
     --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output
          if @@error <> 0 goto error
 end
 else
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', convert(varchar(1000), space(0)) as ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + ''_' + @fieldname + '_detailid]''' + 
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
   --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output 
          if @@error <> 0 goto error    
 end 
   insert into @t_createconcat_fields(sqlno, sqlbody) values(@no, @s) 


        --select 
      if @recordcount = 1
 begin
          set @s1 = ''
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '' declare @col'' + convert(varchar, colno) + ''_detailid  varchar(500) ' + 
                                         ' set @col'' + convert(varchar, colno) + ''_detailid = '''''''''''+ 
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
     --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s1 output
          if @@error <> 0 goto error


   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', @col'' +  convert(varchar, colno) + ''_detailid =   '+
                                            ' case ' +  @strtemp + ' when '''''' + convert(varchar, colvalue) + '''''' then '+
                                            ' @col'' +  convert(varchar, colno) + ''_detailid + ' + @concatfieldname + ' + '''';'''' else ' +
                                            ' @col'' + convert(varchar, colno) + ''_detailid end '''+
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
     --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s2 output
          if @@error <> 0 goto error
 end
 else
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '' declare @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid varchar(500)  ' + 
                                        ' set @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid  = '''''''''''+
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
   --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s1 output 
          if @@error <> 0 goto error    


   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid = ' + 
                                           ' case ' +  @strtemp + ' when '''''' + convert(varchar, colvalue) + '''''' then ' + 
                                           ' @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid + ' + @concatfieldname + ' + '''';'''' else '+
                                           ' @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid end ''' + 
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
   --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s2 output 
          if @@error <> 0 goto error  
 end 
        set @s1 = stuff(@s1, 1, 1, '')
        set @s2 = stuff(@s2, 1, 1, '')
   insert into @t_selectconcat_fields(sqlno, sqlbody_declare, sqlbody_concat) values(@no, @s1, @s2) 


        --update 
      if @recordcount = 1
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + ''_detailid] = ' + 
                                            '@col'' + convert(varchar, colno) + ''_detailid  ''' +
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
     --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output 
          --print @s
          if @@error <> 0 goto error
 end
 else
 begin
   set @nsql = ' set @s = '''''+
               ' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end  + ''_' + @fieldname + '_detailid] = ' + 
                                           ' @col'' +  convert(varchar, colno) + ''_' + @fieldname + '_detailid ''' + 
               ' from '+  @distinctcoltablename + 
               ' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' +  convert(varchar, @edno)  
   --print @nsql
   exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output 
          if @@error <> 0 goto error    
 end 
        set @s = stuff(@s, 1, 1, '')
   insert into @t_updateconcat_fields(sqlno, sqlbody) values(@no, @s) 
      end


      set @bgno = @edno + 1
    end


    --字段汇总
    if @isrowtotal = 1
    begin
   if @recordcount = 1
   begin
     set @s = ',sum(' + @totalfieldname + ') as [rowtotal]'
   end
   else
   begin
     set @s = ',sum(' + @totalfieldname + ') as [rowtotal_' + @fieldname + ']'
   end
set @no = @no + 1
insert into @t_sumcase_fields(sqlno, sqlbody) values(@no, @s)
    end


    select top 1 @i = fieldno, @fieldname = fieldname
    from @t_value_fields
    where fieldno > @i 
    order by fieldno
    set @rowcount = @@rowcount
  end 




  --select * from @t_selectconcat_fields


  declare @sql_1 varchar(8000), @sql_2 varchar(8000), @sql_3 varchar(8000), @sql_4 varchar(8000)
  declare @sql_5 varchar(8000), @sql_6 varchar(8000), @sql_7 varchar(8000), @sql_8 varchar(8000)
  declare @sql_9 varchar(8000), @sql_10 varchar(8000), @sql_11 varchar(8000), @sql_12 varchar(8000)
  select @sql_1 = '', @sql_2 = '', @sql_3 = '', @sql_4= '', @sql_5 = '', @sql_6 =''
  select @sql_7 = '', @sql_8 = '', @sql_9 = '', @sql_10= '', @sql_12 = '', @sql_12 =''




  declare @updateconcatsql varchar(8000), @sqlbody_declare varchar(8000), @sqlbody_concat varchar(8000)
declare @equefieldsql varchar(1000), @tempfieldsql varchar(1000), @strgno varchar(4)


set @equefieldsql = ''
  set @tempfieldsql = @groupbyfields
while len(@tempfieldsql) > 0
begin  
 if charindex(',', @tempfieldsql) > 0
 begin
   set @fieldname = left(@tempfieldsql, charindex(',', @tempfieldsql)-1)
   set @tempfieldsql = right(@tempfieldsql, len(@tempfieldsql)- charindex(',', @tempfieldsql))
 end
 else 
 begin
   set @fieldname = @tempfieldsql
   set @tempfieldsql = ''
 end
 if @equefieldsql <> '' set @equefieldsql = @equefieldsql + ' and '
 set @equefieldsql = @equefieldsql + ' a.' + @fieldname + ' = b.' + @fieldname
end  


  if @concatfieldname <> '' --生成合并字符串表
  begin
 select @sql_1 = sqlbody from @t_createconcat_fields where sqlno = 1
 select @sql_2 = sqlbody from @t_createconcat_fields where sqlno = 2
 select @sql_3 = sqlbody from @t_createconcat_fields where sqlno = 3
 select @sql_4 = sqlbody from @t_createconcat_fields where sqlno = 4
 select @sql_5 = sqlbody from @t_createconcat_fields where sqlno = 5
 select @sql_6 = sqlbody from @t_createconcat_fields where sqlno = 6
 select @sql_7 = sqlbody from @t_createconcat_fields where sqlno = 7
 select @sql_8 = sqlbody from @t_createconcat_fields where sqlno = 8
 select @sql_9 = sqlbody from @t_createconcat_fields where sqlno = 9
 select @sql_10 = sqlbody from @t_createconcat_fields where sqlno = 10
 select @sql_11 = sqlbody from @t_createconcat_fields where sqlno = 11
 select @sql_12 = sqlbody from @t_createconcat_fields where sqlno = 12


    if exists (select * from tempdb.dbo.sysobjects where name = @tablename_concat) 
    begin
      exec('drop table '+ @tablename_concat)  
    end  
 exec(' select identity(int, 1,1) as idno, ' + @groupbyfields + @sql_1 + @sql_2 + @sql_3 + @sql_4 + @sql_5 +  @sql_6 + 
                   @sql_7 + @sql_8 + @sql_9 + @sql_10 + @sql_11 + @sql_12 + 
      ' into ' + @tablename_concat + 
      ' from ' + @sourcetemptablename + 
      ' group by ' + @groupbyfields)
    if @@error <> 0 goto error


 set @nsql = 'set @value = 0
              select  @value = count(*) from ' + @tablename_concat 
 exec sp_executesql @nsql, N'@value int output', @value = @groupcount output 


    set @gno = 1
    while @gno <= @groupcount --分组循环
    begin
      set @strgno = convert(varchar, @gno)
   set @no = 0
   select top 1 @no = sqlno, @updateconcatsql = sqlbody
   from @t_updateconcat_fields
   where sqlno > @no
   order by sqlno 
   set @rowcount = @@rowcount
   while @rowcount = 1 
   begin
        select @sqlbody_declare = sqlbody_declare, @sqlbody_concat = sqlbody_concat
        from @t_selectconcat_fields
        where sqlno = @no


         /*
        print @sqlbody_declare + ' select ' + @sqlbody_concat + 
                    ' from ' + @tablename_concat +  ' a inner join '+ @sourcetemptablename + ' b ' + 
               '      on ' + @equefieldsql + 
                    ' where idno = ' +  @strgno +
                    ' update ' +  @tablename_concat + ' set ' + @updateconcatsql + 
               ' from ' + @tablename_concat  +  ' a '+ 
                    ' where idno = '  + @strgno
         */
   exec(@sqlbody_declare + ' select ' + @sqlbody_concat + 
                    ' from ' + @tablename_concat +  ' a inner join '+ @sourcetemptablename + ' b ' + 
               '      on ' + @equefieldsql + 
                    ' where idno = ' +  @strgno +
                    ' update ' +  @tablename_concat + ' set ' + @updateconcatsql + 
               ' from ' + @tablename_concat  +  ' a '+ 
                    ' where idno = '  + @strgno) 
   if @@error <> 0 goto error

   select top 1 @no = sqlno, @updateconcatsql = sqlbody
   from @t_updateconcat_fields
   where sqlno > @no
   order by sqlno 
   set @rowcount = @@rowcount 
   end  -- end while @rowcount = 1 
    
      set @gno = @gno + 1
    end  -- end while while @gno <= @groupcount


    /*
     exec(' select * from ' + @tablename_concat )   
    --*/
  end


  if exists (select * from tempdb.dbo.sysobjects where name = @insertintotablename) 
  begin
    exec('drop table '+ @insertintotablename)  
  end


  select @sql_1 = sqlbody from @t_sumcase_fields where sqlno = 1
  select @sql_2 = sqlbody from @t_sumcase_fields where sqlno = 2
  select @sql_3 = sqlbody from @t_sumcase_fields where sqlno = 3
  select @sql_4 = sqlbody from @t_sumcase_fields where sqlno = 4
  select @sql_5 = sqlbody from @t_sumcase_fields where sqlno = 5
  select @sql_6 = sqlbody from @t_sumcase_fields where sqlno = 6
  select @sql_7 = sqlbody from @t_sumcase_fields where sqlno = 7
  select @sql_8 = sqlbody from @t_sumcase_fields where sqlno = 8
  select @sql_9 = sqlbody from @t_sumcase_fields where sqlno = 9
  select @sql_10 = sqlbody from @t_sumcase_fields where sqlno = 10
  select @sql_11 = sqlbody from @t_sumcase_fields where sqlno = 11
  select @sql_12 = sqlbody from @t_sumcase_fields where sqlno = 12


  if @concatfieldname = ''
  begin
 if @insertintotablename <> ''
 begin
 exec(' select ' + @groupbyfields + @sql_1 + @sql_2 + @sql_3 + @sql_4 + @sql_5 +  @sql_6 + 
                   @sql_7 + @sql_8 + @sql_9 + @sql_10 + @sql_11 + @sql_12 + 
      ' into ' + @insertintotablename + 
      ' from ' + @sourcetemptablename + 
      ' group by ' + @groupbyfields) 
 
 end
 else
 begin
 exec(' select ' + @groupbyfields + @sql_1 + @sql_2 + @sql_3 + @sql_4 + @sql_5 +  @sql_6 + 
                   @sql_7 + @sql_8 + @sql_9 + @sql_10 + @sql_11 + @sql_12 + 
      ' from ' + @sourcetemptablename + 
      ' group by ' + @groupbyfields) 
 end
    if @@error <> 0 goto error
  end
  else
  begin
    set @tablename_temp = '##crosstable_' + replace(cast(newid() as varchar(40)),'-','') --//生成随机临时表名称 
 if exists (select * from tempdb.dbo.sysobjects where name= @tablename_temp) 
begin
 exec('drop table ' + @tablename_temp)
 end


 exec(' select ' + @groupbyfields + @sql_1 + @sql_2 + @sql_3 + @sql_4 + @sql_5 +  @sql_6 + 
                   @sql_7 + @sql_8 + @sql_9 + @sql_10 + @sql_11 + @sql_12 + 
      ' into ' + @tablename_temp + 
      ' from ' + @sourcetemptablename + 
      ' group by ' + @groupbyfields) 
    if @@error <> 0 goto error


    --exec(' select * from ' + @tablename_temp )   


    set @strtemp = ',' + @groupbyfields
    set @strtemp = replace(@strtemp, ' ', '')
    set @strtemp = replace(@strtemp, ',', 'a.')


 if @insertintotablename <> ''
 begin
 exec(' select ' + @strtemp + @outcrossfields +  @outcrossfields_detail + 
           ' into ' + @insertintotablename + 
      ' from ' + @tablename_temp + ' a inner join ' +  @tablename_concat + ' b ' + 
           '     on '  + @equefieldsql) 
 
 end
 else
 begin
 exec(' select '+ @strtemp + @outcrossfields +  @outcrossfields_detail + 
        ' from ' + @tablename_temp + ' a inner join ' +  @tablename_concat + ' b ' + 
           '     on '  + @equefieldsql) 
 end
    if @@error <> 0 goto error


 if exists (select * from tempdb.dbo.sysobjects where name= @tablename_temp) 
begin
 exec('drop table ' + @tablename_temp)
 end
  end
  return 0 


  error:
    begin
      select @errormsg = dbo.f_geterror('99999') --99999系统内部错误。
      raiserror(@errormsg, 16, 1) with nowait
      return -1
    end     
end
go






/*


select * from  dbo.t_sys_errorcode


exec  p_pubcrosstable '', 'vw_employee', 'simname', 'sex', 'emp_id', '' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'sex', 'sex', '' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'workno', 'emp_id', '' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'sex', 'emp_id,dept_id', '' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'sex,gzcls', 'emp_id', '' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'gzclsname,sex', 'emp_id', '' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'gzclsname,sex', 'emp_id,dept_id', '' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'sex,gzclsname,gradename,position,native', 'emp_id,dept_id', ''


exec  p_pubcrosstable '', 'vw_employee', 'simname', 'position', 'emp_id' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'position', 'emp_id', 1, 0, '<iscount>1</iscount>' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'position', 'emp_id', 0, 0, '<iscount>1</iscount><concatfieldname>cname</concatfieldname>' 
exec  p_pubcrosstable '', 'vw_employee', 'simname', 'position', 'emp_id', 0, 0, '<insertintotablename>##abc</insertintotablename><iscount>1</iscount><concatfieldname>workno</concatfieldname>' 


exec  p_pubcrosstable '', 'vw_employee', 'simname', 'gzclsname,sex', 'emp_id,dept_id', 0, 0, '<iscount>1</iscount><concatfieldname>workno</concatfieldname>' 


exec  p_pubcrosstable ' select workno, cname, simname, position, effectday, gzcls, kqtypename,##kqtotalanalyseitem_tj.itemname,##hrperiob_tj.periobname, totalvalue  from ##kqtotalanalyse_tj left join ##hrperiob_tj   on ##kqtotalanalyse_tj.timeunit = ##hrperiob_tj.periobsn left join ##kqtotalanalyseitem_tj   on ##kqtotalanalyse_tj.totalitem = ##kqtotalanalyseitem_tj.itemcode    group by workno, cname, simname, position, effectday, gzcls, kqtypename, ##kqtotalanalyseitem_tj.itemname, ##hrperiob_tj.periobname, totalvalue    ', '', 'workno;cname;simname;position;effectday;gzcls;kqtypename;itemname', 'periobname;', 'totalvalue', 0, 0, '<INSERTINTOTABLENAME>##cross_sunq_57</INSERTINTOTABLENAME>'






exec  p_pubcrosstable ' select workno, cname, simname, position, effectday, gzcls, kqtypename,##kqtotalanalyseitem_tj.itemname,##hrperiob_tj.periobname, totalvalue  from ##kqtotalanalyse_tj left join ##hrperiob_tj   on ##kqtotalanalyse_tj.timeunit = ##hrperiob_tj.periobsn left join ##kqtotalanalyseitem_tj   on ##kqtotalanalyse_tj.totalitem = ##kqtotalanalyseitem_tj.itemcode    group by workno, cname, simname, position, effectday, gzcls, kqtypename, ##kqtotalanalyseitem_tj.itemname, ##hrperiob_tj.periobname, totalvalue    ', '', 'workno;cname;simname;position;effectday;gzcls;itemname', 'periobname;kqtypename;', 'totalvalue', 0, 0, '<INSERTINTOTABLENAME>##cross_sunq_57</INSERTINTOTABLENAME>'




*/