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

Transact-SQL (2000)

Reverse Pivot Table to UDF?

Author

Topic

Silenz
Starting Member

7 Posts

Posted - 2005-08-18 : 16:00:20

I accidently posted this in the Developers Section where I found this nice code.

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.


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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources