Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as primarily opinion-based by Max Vernon, Colin 't Hart, Paul White, Mark Storey-Smith, RolandoMySQLDBA May 31 at 1:18

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

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 May 22 '13 at 10:57
@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 May 22 '13 at 11:23