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

score 7 · Answer 1 · answered Sep 09 '16 at 08:26

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.

score 1 · Answer 2 · answered Sep 09 '16 at 07:12

Given what your trying to do I believe the only option is to use a stored procedure for this. Views are limited to SELECT statements with CTE's and functions. More complex executions won't work.

A couple of other ideas:

1) If you want to hide the workings you could insert the result set into a table and maybe schedule your procedure with the dynamic code to run daily in an agent job or something to refresh the table.

2) Have a look at database triggers and refresh the table with new data at the point a user runs a SELECT against it. This isn't ideal and would course performance issues if you have lots of users requiring the same dataset so very much a dirty workaround. In fact why am I even suggesting this.

Using Dynamic Pivot SQL in a View

2 Answers2