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.
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
What you say makes sense, and you touched one of the areas
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
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
The backdoor is to use the OPENQUERY function, referring to
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:
Then you can issue a query against the OPENQUERY function,
defining a view based on that query:
But again, since this technique is an
undocumented/unsupported backdoor, I
wouldn’t recommend using it.