Using Dynamic Pivot SQL in a View

Question

Gday all

I wrote some code for dynamically spreading a table like SQL Server: Transposing rows into 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 fine, but I want to use it in the view, I tried to copy the code into the view, and it works, but it doesn’t save, because it doesn’t like Declare statements in the view, I got it working in the Stored procedure, but I can’t use the stored ones procedures in the view, I think I need to have it as a function with table values, but I can not use the Execute operator in TBF. I need to combine this data with another table in the view and want to keep it dynamic, so any ideas would be much appreciated :) We use SQL 2008 R2

Answer 1

(myView ), , , :

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

SP .

Answer 2

, , , . SELECT CTE . .

:

1) , , , , -, .

2) , SELECT. , , , . , .