I would really like to implement a simple pivot capability in Dremio. I’ve done this in postgres using https://github.com/hnsl/colpivot with some good success but would love to leverage Dremio for improved performance.
Ideally I could create sql with a CTE that groups down the dataset dimensions and computes a single aggregate measure, then create a pivot by selecting a function that takes arguments specifying the CTE columns to be the pivot rows, pivot columns (or classes) and the column to be the values.
Can you give me pointers to how that could be done as a Java UDF? I see in the dremio-oss codebase there are some pivot classes in com.dremio.sabot.op.common.ht2. Can these be enabled or extended in some way to enable PIVOT functionality?