title: Using Dynamic Pivot SQL in a View
body: <p>Gday All,</p>
  
  <p>I've written some code to dynamically Pivot a table like <a href="https://stackoverflow.com/questions/20111418/sql-server-transpose-rows-to-columns">SQL Server : Transpose rows to columns</a></p>
  
  <p>The code looks like this</p>
  
  <pre><code>DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)
  
  SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(FieldName)
              FROM CORE_Items_Extra
              WHERE Not(FieldName = '')
              ORDER BY 1
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)'),1,1,'')
  
  SET @sql = 'SELECT ItemID, ' + @cols + '
                FROM
              (
                SELECT ItemID,  FieldValue, FieldName
                  FROM CORE_Items_Extra
              )  AS SourceTable
              PIVOT
              (
                MAX(FieldValue) FOR FieldName IN (' + @cols + ')
              ) AS PivotTable;'
  
  EXECUTE(@sql)
  </code></pre>
  
  <p>Which works perfectly BUT I want to use it within a View, I've tried copying the code to a view and it works but wont save as it doesn't like the Declare statements in the view, I've got it working in a Stored Procedure but cant use Stored Procedures in a View, I think I need to have it as a Table-Valued Function but cant use the Execute statement within a TBF. I need to combine this data with another table in a view, and would like to keep it dynamic, so any ideas would be greatly appreciated :) We are using SQL 2008 R2</p>