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

Follow

Skunpoj Thanarojsophon

• March 31, 2022 17:40

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

https://www.oracle.com/technical-resources/articles/database/sql-11g-pivot.html

Thanks for your kind support.

Best regards,

0

• 

  Candido Dessanti

  • April 01, 2022 09:57

  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

  0

  Comment actions Permalink
• 

  Skunpoj Thanarojsophon

  • April 01, 2022 10:24

  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.

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • April 02, 2022 08:46

  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

  0

  Comment actions Permalink
• 

  Skunpoj Thanarojsophon

  • April 02, 2022 10:12

  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.

  0

  Comment actions Permalink
• 

  Skunpoj Thanarojsophon

  • April 02, 2022 10:49

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

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • April 03, 2022 11:13

  Yap, probably this is doable with an UDTF.

  The reply is maybe ;)

  0

  Comment actions Permalink

Please sign in to leave a comment.