Cross Reference Or Pivot Table

Hi All,

The problem is about cross reference.

1. I have a third party cross reference store procedure SimpleXTab


CREATE       PROCEDURE [dbo].[SimpleXTab2]
@XField varChar(50),
@XTable varChar(100),
@XWhereString varChar(250),
@XFunction varChar(10),
@XFunctionField varChar(50),
@XRow varchar(300),
@ResultTable varchar(100)
 AS
Declare @SqlStr nvarchar(4000)
Declare @tempsql nvarchar(4000)
Declare @SqlStrCur nvarchar(4000)
Declare @col nvarchar(100)

set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + ']  ' + @XWhereString + ' Group By [' + @XField + ']'

/* select @sqlstrcur */
exec sp_executesql @sqlstrcur

 

 declare xcursor Cursor  for  Select * from ##temptbl_Cursor

 open xcursor


 Fetch next from  xcursor
 into @Col
 

While @@Fetch_Status = 0
Begin
  set @Sqlstr = @Sqlstr + ", "
  set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col +
                           "' then [" + @XFunctionField + "] Else 0 End) As [" +  @Col + "]" ,'')
  set @Sqlstr = @tempsql
  Fetch next from xcursor into @Col
 
End


 /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */

 set @tempsql = 'Select  '  + @XRow + ', ' + @Sqlstr + 'into ' +@ResultTable+' From ' + @XTable +
                             @XWhereString +  ' Group by ' + @XRow
print @tempsql
 set @Sqlstr = @tempsql

 Close xcursor
 Deallocate xcursor 

  set @tempsql = N'Drop Table ##temptbl_Cursor'
  exec sp_executesql @tempsql
print @tempsql
 /*  Select @Sqlstr as [mk], len(@sqlstr) as [leng] */
print @sqlstr
   exec sp_executesql @Sqlstr

if @@rowcount = 0  select 'No Records found'
GO

2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function.
    how can i modify SimpleXtab to avoid using math function but still can generate cross reference.

   exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'

 

Many Thanks!