Is pivot/unpivot (like in SQL Server / ORACLE) supported?

I am looking for an example for an omnisql query which does something like in this example

Thanks for your kind support.

Best regards,

HI @kimgiftww ,

Welcome to the community!

We haven’t PIVOT or UNPIVOT function on our database, but you can “emulate” them if you know the values in advance

e.g.

select uniquecarrier, 
       round(avg(case when flight_dayofweek = 1 then depdelay else null end),1) as mon, 
       round(avg(case when flight_dayofweek = 2 then depdelay else null end),1) as tue,
       round(avg(case when flight_dayofweek = 3 then depdelay else null end),1) as wed,
       round(avg(case when flight_dayofweek = 4 then depdelay else null end),1) as thu,
       round(avg(case when flight_dayofweek = 5 then depdelay else null end),1) as fri,
       round(avg(case when flight_dayofweek = 6 then depdelay else null end),1) as sat,
       round(avg(case when flight_dayofweek = 7 then depdelay else null end),1) as sun
From flights_2008_7m group by 1;

Regards,
Candido

Hi, thanks for sharing.

I am using pivot because I don’t know the value to be pivoted as columns in advance.
The values are hundreds of distinct dates. This is why I am looking for the pivot functionality.

Hi,

I’m going to ask internally, but I think that it’s not pèossible to add such functionality with a custom table function; probably it’s doable programmatically with python (?).

I will come back to you if I had something to share.

Candido

Hi,

Thanks for taking it to consideration. I am currently do it with pandas and was looking for a way to do it using GPU. (Will also try Dask or other parallelisation tools)

As I was using heavydb as our data warehouse, I thought this was something we could do it during the data transformation process.

Doing the pivot in python would require exporting the whole data to fit into cuDF and this may be something we could not afford.

We would like to utilise and rely on the layer (gpu ram disk) optimization that HeavyDB provides.

Instead of the pivot, is it possible to keep the result of the groupby aggregation to an array column?

Yap, probably this is doable with an UDTF.

The reply is maybe :wink: