TL;DR What is the reason that in 2013 we still can't define a VIEW that displays some pivoted data with a dynamic column set.

TL;DR/2 Is pivoting data a denormalization technique?

First: Not going to assert anything. Maybe someone does dynamic pivot, but atm. it seems neither MSSQL nor Oracle nor anyone else does it. (Well, Oracle does support dynamic pivot columns with the XML option.)

Second: Obviously vendors haven't thought it important enough / not enough customer pressure. This question is exactly about the why.

Third: Dynamically building a query string that contains the right columns and then EXECUTEing it doesn't strike me as very nice.

This question more or less exactly describes the problem I think I'm facing.

... a main table and a child table ... I want to show the user a single table in a grid view which includes rows from the main table and rows from the child table, with the latter one being shown as additional columns.

There are two approaches to solving this, the classical approach with CASE WHEN and the newer approach with the PIVOT database feature.

The strange thing I have found (understood) so far is that the PIVOT approach seems to be exactly as limiting as the CASE WHEN approach, that is, if I define a VIEW that displays some pivoted data, all columns need to be hardcoded in advance.

As someone put it:

... by saying that I agree that pivoting data is a display function and is best done at the application layer or in reporting software like ...

But as far as I can grasp this, the "need" to pivot data comes from the "need" to first normalize the data in a RDBMS, that is, the "best" way to store data in an RDBMS is in a normalized form, but that isn't the "best" way to consume the data - viewing or processing the data would normally done on some "joined" / denormalized view of the data, and as far as I can tell pivoting the data is a good way of doing so for some datasets.

This leads me to the question why pivoting data isn't better supported by the "DB layer", for instance by being able to (what the Oracle PIVOT XML supports) specify a dynamic set of columns to pivot.

  • I'll flip the question back to you... How would you go about parsing any query that contained a pivoted view with a dynamic number of columns? How would you reference these columns, as they are unnamed? You'd also have to hard parse every single query involving the view every time they were executed, maybe in several stages after the numbers of columns in the resultset that makes the view are known - hiw would you even deal with that? It'd perform like a dog, for starters...
    – Philᵀᴹ
    Commented May 22, 2013 at 10:57
  • 1
    @Phil - thanks for your input. I don't have answers, but keep in mind that Oracle does support this kind of thing for the PIVOT XML result, so they apparently figured out all the parsing stuff(?) -- As for performance: Today, the solution is to dynamically build the query(-string) and then EXECUTE it. That can only be worse performance wise, or can it?
    – Martin
    Commented May 22, 2013 at 11:23


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.