How to aggregate a string in Toad Datapoint?

Toad Data Point & Toad Intelligence Central
1

Hi,

We are testing out Toad to see whether it can replace Hyperion Brio for us. One of the features of Brio was that one could use the SUM() function to aggregate strings, not just sum numbers; a “SUM(Managers,Location)” would yield a result were, for each location, all the managers would be shown, in a comma separated fashion. In Toad the SUM() function seems to only work for numbers and I have not been able to find a way to approach this, I have tried as calculated field in the query, as a Transform and Cleanse step, Pivot and in a Report design. Does anyone know a way, it should not be a very uncommon way of aggregating data.

Thanks!

2

You can do this in the sql. How depends on the platform you are using. The ListAgg function in Oracle will give you a delimted set of values in a column (comma, pipe, whatever you specify). SQL server can do the same thing with the Stuff function (just uses more code to do it).

Example of ListAgg: https://stackoverflow.com/questions/13584250/sql-using-listagg-and-group-by-non-duplicated-values

Example of Stuff: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server

That is the easiest way I know to do it.

3

Thanks, Listagg() worked in the calculated column part of the query!