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!

no replying. I didn't describe clearly?

|||

What did you want it to do with multiple values? If there is only one value per row/column, use MAX or MIN.