https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/tools/RelBuilder.java Here in RelBuilder class documentation for pivot() method provided example is below. for SQL ::
SELECT * FROM (SELECT mgr, deptno, job, sal FROM emp) PIVOT (SUM(sal) AS ss, COUNT(*) AS c FOR (job, deptno) IN (('CLERK', 10) AS c10, ('MANAGER', 20) AS m20))
RelBuilder.pivot(groupKey, aggCalls, axes, valueMap.build().entrySet()) can be used to create a RelNode object. Here we pass a Map(alias, pivot_column_values) object as "values" parameter.
final Function<RelBuilder, RelNode> f = b -> b.scan("emp")
.pivot(b.groupKey("MGR"), Arrays.asList(b.sum(b.field("SAL")).as("SS"), b.count().as("C")),
b.fields(Arrays.asList("JOB", "DEPTNO")),
ImmutableMap.<String, List<RexNode>>builder()
.put("C10", Arrays.asList(b.literal("CLERK"), b.literal(10)))
.put("M20", Arrays.asList(b.literal("MANAGER"), b.literal(20))).build().entrySet())
.build();
How to pass "values" parameter when we don't have alias for pivot column values.
e.g. how to pass "values" parameter of RelBuilder.pivot() method for below query
SELECT * FROM (SELECT mgr, deptno, job, sal FROM emp) PIVOT (SUM(sal) AS ss, COUNT(*) AS c FOR (job, deptno) IN (('CLERK', 10), ('MANAGER', 20)))
Also is it compulsory to have at least one entry in values Map?