Views and Dynamic PIVOT

I got the following question from a reader regarding the article: PIVOT on Steroids (January 2007). Since the question and the response might be of interest to other readers, I decided to blog about it.

Question:

“Hi there,

I have just read your great article on extending the pivot facility, I found it particularly interesting as my application requires a dynamic pivot capability which I haven't been able to figure out how to do in SQL Server 2005.  Your sp_pivot procedure goes a long way to satisfying my requirement however I have one final problem that I can't see a way around.  I thought you might be able to help.

    Essentially I need a way that my users can define pivots of their data and then to be able to access that pivoted data through views.  My application is a reporting system that collects data and provides a query building and charting interface along with a bunch of other stuff.  So the users define what data is going into it, set up views of the data using
the query builder (which could be views of views etc.) then chart it and include it in documents etc.  The documents and charts link back to the views so that when the document is opened again all the data is refreshed automatically.  This is why I need facilities that allow data to be pivoted through views.  I have looked at various options, I thought UDF might be the go but EXEC is not allowed in UDF.

    Does what I say make sense here, do you know of any way that a stored proc can be selected from or accessed in a view or table returning function?”

Answer:

What you say makes sense, and you touched one of the areas that complicated things for the developers of SQL Server when they considered the concept of dynamic pivoting.

I assume that one of the reasons that PIVOT doesn’t support dynamic resolution of the rotation elements is because a truly dynamic PIVOT would have meant a query with a dynamic schema, and this is not allowed in a view (or any table expression, including a table UDF).

There’s no simple supported way around this. There is a backdoor, but it’s undocumented and unsupported, hence I wouldn’t recommend using it in production systems.

The backdoor is to use the OPENQUERY function, referring to the local instance as if it were a linked server. OPENQUERY returns a table result and is allowed in a view.

In order to allow referring to the local instance as a linked server, you first turn on the 'Data Access' server option:

EXEC sp_serveroption [local_server_name], 'Data Access', 'True';

Then you can issue a query against the OPENQUERY function, including defining a view based on that query:

CREATE VIEW dbo.V1ASSELECT * FROM OPENQUERY([local_server_name],  N'SET FMTONLY OFF;  EXEC Northwind.dbo.sp_pivot    @query    = N'dbo.Orders',    @on_rows  = N'EmployeeID AS empid, YEAR(OrderDate) AS order_year',    @on_cols  = N'MONTH(OrderDate)',    @agg_func = N'COUNT',    @agg_col  = N'*';') AS O;GOSELECT * FROM dbo.V1;

But again, since this technique is an undocumented/unsupported backdoor, I wouldn’t recommend using it.

Regards,
Itzik