Using Dynamic Pivot SQL in a View

Question

Gday All,

I've written some code to dynamically Pivot a table like SQL Server : Transpose rows to columns

The code looks like this

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)

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

Answer 1

I suggest you to create some view (myView in sample below) and then write stored procedure that will alter your view like:

CREATE VIEW myView
AS
SELECT 1
GO

CREATE PROCEDURE myStoredProc
AS
BEGIN
    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 = 'ALTER VIEW myView
                AS
                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)
END
GO

Run this SP within job once or more in a day.