Table<Record> crosstab(String);Table<Record> crosstab(String, Object...);Table<Record> crosstab(Select<?>);
--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Hey Lukas,
Thanks for that pointer - do you have an example of how to use it?
On a related tangent, I was wondering (under the assumption that the
dataset is small enough) whether I could run an instance of XE instead
of Postgres....
public class PostgresWrapper
{
public static Result<Record> pivot(DSLContext context, Select<?> raw, Select<?> crossTabValues)
{
List<Field<?>> resultFields = new ArrayList<Field<?>>();
//The result will contain all but the last two columns for the raw fields.
Field<?> []rawFields = raw.fields();
for (int i = 0; i < rawFields.length - 2; i++)
{
resultFields.add(rawFields[i]);
}
//And then one column for each cross tab value specified
Result<?> crossTabHeadings = context.fetch(crossTabValues);
for (Record r : crossTabHeadings)
{
resultFields.add
(
DSL.field
(
r.getValue(0).toString(),
rawFields[rawFields.length - 2].getDataType(context.configuration())
)
);
}
//And postgres requires that the names of the resultant fields be specified
// explicitly, using 'ct' <"Name of Field", type> pairs...
StringBuffer ctList = new StringBuffer();
for (int i = 0; i < resultFields.size(); i++)
{
ctList.append
(
"\"" + resultFields.get(i).getName() + "\" "
+ resultFields.get(i).getDataType(context.configuration())
.getTypeName(context.configuration())
);
if (i < resultFields.size() - 1)
{
ctList.append(", ");
}
}
return
context
.fetch
(
"select * from crosstab('"
+ raw.getSQL(ParamType.INLINED).replace("'", "''") + "', '"
+ crossTabValues.getSQL(ParamType.INLINED).replace("'", "''")
+ "') as ct(" + ctList.toString() + " )"
);
}
}
DSLContext context = ...
Select<?> raw =
DSL.select
(
DSL.concat(DELIVERY_PARTICIPANTS.DELIVERY_ID, DELIVERY_PARTICIPANTS.EMAIL),
DELIVERY_PARTICIPANTS.DELIVERY_ID,
DELIVERY_PARTICIPANTS.EMAIL,
DELIVERY_PARTICIPANTS.COHORT,
DSL.val("Yes").as("Enrolled")
)
.from(DELIVERY_PARTICIPANTS)
.where(DELIVERY_PARTICIPANTS.DELIVERY_ID.eq(deliveryID))
.orderBy(1, 2, 3);
Select<?> crossTabValues =
DSL.select(DELIVERY_COHORTS.COHORT) //these are is known to be distinct
.from(DELIVERY_COHORTS)
.orderBy(DELIVERY_COHORTS.DISPLAY_ORDER);
return PostgresWrapper.pivot(context, raw, crossTabValues);
return
context
.fetch
(
"select * from crosstab('"
+ raw.getSQL(ParamType.INLINED).replace("'", "''") + "', '"
+ crossTabValues.getSQL(ParamType.INLINED).replace("'", "''")
+ "') as ct(" + ctList.toString() + " )"
);
}
}
context.fetch(resultQuery("select * from crosstab({0}, {1}) as ct({2})",val(raw.getSQL(ParamType.INLINED)),
val(crossTabValues.getSQL(ParamType.INLINED)),
ctList // If you construct it using DSL.list()
));
Any feedback on this approach would be appreciated. I couldn't work out a way to make it more type-safe, and I'm not sure that is possible.
//And then one column for each cross tab value specified
Result<?> crossTabHeadings = context.fetch(crossTabValues);
for (Record r : crossTabHeadings)
{
resultFields.add
(
DSL.field
(
r.getValue(0).toString(),
rawFields[rawFields.length - 2].getDataType(context.configuration())
)
);
}
O.K. So I'm having a couple of issues trying to flesh these suggestions out.Two main things:(1) I couldn't find DSL.list(QueryPart). There is DSL.listAgg, but that doesn't seem like the right thing.
My other attempt at using the templating as per your suggestion ended up quoting the entire contents of the ct() function call as one string. So, I kind of gave up. Ff there's an obvious mistake I'm making, please let me know.
(2) The other thing I'd like to do is grab the resultQuery, and rather than just fetch from it, I'd like to join against it making new query. Is that even possible? What I think I need to do is somehow make a TableLike out of the resultQuery value, but I can't find a way forward there.
I have workarounds to both of these (the workaround for (1) is just to leave things as they are, and the workaround for (2) is to make very complicated raw and crosstabValues queries, which only works sometimes).
Any insight would be appreciated, but I don't want to take up too much of your time.
No worries! This could be useful to many other users, I'd like to know more about this. Perhaps, we'll even be able to emulate Table.pivot() via crosstab for PostgreSQLCheers,
Lukas
I'll try to come up with a small example of what I'm trying to achieve in (2) above, and perhaps that will be a good way to discuss it further. I had a quick go, but I made the example too simple, and so it didn't make much sense. I'll see if I can get to it over the weekend. (We're releasing at the moment, so time's at a bit of a premium).
--
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 jooq-user+unsubscribe@googlegroups.com.