In an old idea 5 years back from Bas Hopstaken, where he suggest to
I would like to add to this to add the Pivot function to. So you can do it both ways without having to create a view.
SUBMITTED
Pivot and Unpivot
+6
+6I totally agree on this. Still waiting for this idea to be added to the “advanced features list”.
+6Here's the link to the idea of my colleague Bas
+8It would indeed be useful to have a transpose feature available to turn columns into rows and vice-versa.
Given that this is often applied against raw data it might be useful to automate this through ADF: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-pivot / https://learn.microsoft.com/en-us/azure/data-factory/data-flow-unpivot . Qlik Sense has always had a nice visual way of doing this (CROSSTABLE LOAD).
On the other hand I have frequently seen expert opinion stating that this is a front-end problem and not something to be addressed in your database, though that may be stated from an OLTP bias. If you are unlucky enough to have to work against an EAV modelled system, this would be quite important.
I typically use SQL pivot/unpivot in the context of parent-child hierarchical data where there isn't really a generic way to approach this, but I think for transposing data it should be possible to come up with a generic feature.
+6I do agree with
+4I am trying to apply to currency conversions and definitely need this feature.
As a solution we can use a custom view in TX as this:
The table DSA.EuroFXRef-Hist comes from:
And it’s like:
One column for each currency to convert to EUR.
But in my ERP I’ve only a few currencies (in the table DSA.Currencies:
With the view I can get the data unpivoted for only the curencies that we are using:
Hope this helps.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
