We have some data sources that provide columns in a row-wise format. For example:
key value
----- ------
k1 v1
k2 v2
I'd like to dynamically construct an inline view that rotates the data such that:
k1 k2
--- ---
v1 (null)
(null) v2
I've been trying to come up with a clever way to use TEXTTABLE or ARRAYTABLE to perform the rotation but I've run out of clever-juice. Any thoughts? It looks like there is no way to write the equivalent of ARRAYTABLE in a translator (ie, a function/procedure that produces a dynamically constructed result set as, I suspect, the equivalent of a dynamically constructed view).
The issue is the column naming. We don't have a facility that will let you dynamically create the column names. Just changing a column into a row is fairly straight-forward if there is a fixed row count.