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
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.