Views and Dynamic PIVOT

SQL Server Pro

Itzik Ben-Gan

Fri, 01/12/2007 - 11:00pm

I got the following question from a reader regarding the article: PIVOT on
Steroids (January 2007, InstantDoc #94268). 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.V1

AS

SELECT * 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;

GO

SELECT * FROM dbo.V1;

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

Regards,
Itzik

Syndicate