SQL Function that returns dynamically pivotted data

Question

Is it at all possible to have a table valued function in MSSQL that takes an attribute and generates an associated SQL statement with Pivot function?

CREATE FUNCTION dbo.fnPivot (@EntityTypeID int)
RETURNS TABLE
AS
BEGIN
    DECLARE @SQL varchar(MAX);
    DECLARE @COLS varchar(MAX);

    select @COLS=coalesce(@COLS+',','')+'['+Name+']'from c_EntityAttribute WHERE EntityTypeID = @EntityTypeID;

    SET @SQL = 'SELECT * FROM (SELECT EntityInstanceID, AttributeName, Value FROM v_EntityElementData WHERE EntityTypeID = 1) as s';
    SET @SQL = @SQL + 'PIVOT ( MIN(Value) FOR AttributeName IN (' + @COLS + ') ) AS p';

    RETURN EXEC sp_ExecuteSQL @SQL ;
END

Answer 1

Unfortunately not, with the exception of stored procedures SQL Server keeps track of the table definition of the output of all views, functions etc. Thus the columns and types can't change dynamically on the input.

For this reason I've never found the PIVOT operator to be useful. Generally the only way to get varying column data out is to treat it as XML.

For what reason are you pivoting it? This is usually a UI concern, and so I would recommend doing it at the UI, or SSRS etc.

Answer 2

A table valued function must always return a specified schema, so this isn't possible.