Hi Ben, It's actually possible to emulate PIVOT using dynamic SQL. jOOQ emulates Oracle's / SQL Server's PIVOT clause in Table.pivot(), but the implementation is rather experimental (and not officially supported) - so I wouldn't exclude bugs:
- http://www.jooq.org/javadoc/latest/org/jooq/Table.html#pivot(org.jooq.Field.. .) - https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/Pivot.java See the implementation above in org.jooq.impl.Pivot.DefaultPivotTable Any help to improve this situation is greatly appreciated, of course :-) Cheers Lukas 2014-04-16 11:50 GMT+02:00 Ben Hood <[email protected]>: > Hi Lukas, > > It is mysterious, but it is not at all powerful :-( > > It seems like just a hack in Postgres to emulate pivot tables. > > My requirement is to transpose rows in query to columns, taking this > query result as an example > > subscription description charge sum count > ------------ -------------------- --------- -------- ----- > 13973 National 0 28686876 4 > 13973 Mobile National 322416 4836 68 > 13973 SMS 83600 0 44 > 13973 International 640 12 4 > > I'd like to flatten these four rows into one row, grouped by subscription, > i.e. > > subscription national_charge national_sum national_count > mobile_national_count .... > ---------------- ----------------------- ------------------- > --------------------- ------------------------------- > 13973 0 28686876 4 > 322416 > > I'm pretty sure I've done this with Oracle before, but I'm stuck with > Postgres. > > Maybe there's a better way that I can create some kind of dynamic pivot? > > Cheers, > > Ben > > On Wed, Apr 16, 2014 at 10:10 AM, Lukas Eder <[email protected]> wrote: > > Hi Ben, > > > > I wasn't aware of this PostgreSQL. Looks mysterious and powerful! I have > > registered a feature request to support those functions: > > https://github.com/jOOQ/jOOQ/issues/3190 > > > > I guess, jOOQ's PostgresDSL could be enhanced to have functions like: > > > > Table<Record> crosstab(String); > > Table<Record> crosstab(String, Object...); > > Table<Record> crosstab(Select<?>); > > > > > > ... and then, also support for crosstabN and the other overloads. > > > > What's your use-case? Do you know how these functions perform? > > > > Cheers > > Lukas > > > > > > 2014-04-16 10:41 GMT+02:00 Ben Hood <[email protected]>: > >> > >> Hey Lukas, > >> > >> I was wondering if you've come across an example of using crosstabs in > >> Postgres with JOOQ? > >> > >> Cheers, > >> > >> Ben > >> > >> -- > >> You received this message because you are subscribed to the Google > Groups > >> "jOOQ User Group" group. > >> To unsubscribe from this group and stop receiving emails from it, send > an > >> email to [email protected]. > >> For more options, visit https://groups.google.com/d/optout. > > > > > > -- > > You received this message because you are subscribed to the Google Groups > > "jOOQ User Group" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [email protected]. > > For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
