Pivot function?

1

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?

2 Likes
2

Hey @ Jeff_Gerber , did you never get a solution to the pivot function ?

3

yes, we implemented our one wrapper that does a group by CET and then a select for every column. thanks.

4

Do mind sharing pseudo-code of your implementation, I try using this subquery which is similar to using a CTE see below. I get null and duplicated rows, A Sql Server implementation of the PIVOT was without nulls. see attached.

Screen Shot 2021-05-17 at 2.21.01 PM
Screen Shot 2021-05-17 at 2.21.01 PM824×522 11.9 KB 

    Select distinct ID, NAME, ST1, ST2,
    case when DOMAIN='SALES ' then max(RV_ME) end as SALES,
    case when DOMAIN='BROKER' then max(RV_ME) end as BROKER
    FROM
    (
    select distinct cr.ID, d.NAME, cr.ST1, cr.ST2, da.DOMAIN, rc.RV_ME
    from
    Tbl-1 cr
    join Tbl d on d.ab = cr.bd
    join Tbl1 rv on rv.cf = cr.ed
    join Tbl2 da
    on da.ef = rv.df and da.DOMAIN in ('SALES','BROKER')
    join Tbl3 rc
   on rc.RV_DOMAIN = da.DOMAIN_CD
    and rc.RV_CD = rv.VALUE_TX
    and rc.RV_DOMAIN in ('SALES','BROKER')
    )
    GROUP BY DOMAIN,ID, NAME, ST1, ST2, RV_ME