title: SQL Function that returns dynamically pivotted data
body: <p>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?</p>
  
  <pre><code>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
  </code></pre>