Reverse Pivot Table to UDF? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reverse Pivot Table to UDF?

Author

Topic

Silenz
Starting Member

7 Posts

Posted - 2005-08-18 : 15:50:48

Anyone able to make this code a User Defined Function to return a table?

[Code]
DECLARE @sql varchar(2000), @table varchar(50)
SELECT @sql = '', @table = 'myTable'
SELECT @sql = @sql + 'SELECT ID, ''' + column_name + ''' AS Question,
Convert(varchar(50),' + column_name + ') AS Answer FROM ' + table_name + ' UNION '
FROM information_schema.columns
WHERE table_name=@table AND column_name<>'ID'
SELECT @sql = Left(@sql,Len(@sql)-5)
EXEC (@sql)[/Code]

I tried this but gave an error stating can't use EXECUTE to fill Table Variable.

[Code]
Create Function dbo.UnCrossEvalQuesPoints (@FormNmbr nvarchar(3),@Loc nvarchar(3))
Returns @EvalQuesPoints Table
(TBL_ID int,
Eval_ID int,
Ques_Nmbr int,
Points nvarchar(50))

AS
Begin

DECLARE @sql nvarchar(8000)
SELECT @sql = ''
SELECT @sql = @sql + 'SELECT ''' + @FormNmbr + ''' as TBL_ID, Eval_id, ''' + Right(column_name,4) + ''' AS Ques_Nmbr,
Convert(varchar(250),' + column_name + ') AS Points FROM ' + table_name + ' UNION '
FROM information_schema.columns
WHERE table_name = @Loc + '_EvalQuesPointsData' + @FormNmbr AND column_name <> 'Eval_id'
SELECT @sql = LEFT(@sql, Len(@sql) - 5)

Insert Into @EvalQuesPoints
exec @sql

Return
End[/Code]

gpl
Posting Yak Master

195 Posts

Posted - 2005-08-22 : 06:27:24

Sorry, you cannot use Exec in a UDF as it is non-deterministic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources