move rows to columns "pivot or crosstab"

Support
#1

Hi, I would like to know your opinion about the best approach to the problem that I present to you:

  • I have an entity defined as follows:
    Id uuid
    Description1 string
    Description2 string
    DescriptionMeasure string
    ImportMeasure bigdecimal

Example data:
3ab4955 …, leganes, butcher, sales, 2000
45bxt333 …, leganes, butchers, shopping, 500

  • I would like to move the measures to columns as follows: “pivot rows to columns”:
    Id description1 description2 descriptionMeasure descriptionMeasure …
    2adx3… leganes butcher sales shopping …

  • Does jpql support this type of queries? (Crosstab or pivot)

-I would like to represent it in a table, the number of measures is not fixed, it is variable so I would have to construct the table dynamically based on the fields returned by the query.

  • I would need to do calculations dynamically based on fields returned by the query (I have to think how to do it) and represent them, as these calculations have to be made on the fly, could I support addGeneratedColumn?

  • I would use GroupTable since I need to summarize the columns for each of the groups, can you summarize the columns built dynamically and with generatedColumn ?

Thanks and regards.

#2

You can try to do this by creating a ValueDatasource and a GroupTable dynamically. Something like this:

ValueGroupDatasourceImpl ds = DsBuilder.create().buildValuesGroupDatasource();
ds.addProperty("prop1").addProperty("prop2").addProperty("prop3", BigDecimal.class);
ds.setQuery("...");
ds.refresh();

Table table = componentsFactory.createComponent(GroupTable.class);
table.setDatasource(ds);
table.setColumnCaption("prop1", "Property 1");
table.setColumnCaption("prop2", "Property 2");
table.setColumnCaption("prop3", "Property 3");

table.setWidth("100%");
table.setHeight("100%");

someBox.add(table);