collectString
Use this function in a SQL Template Designer query or in the Method Designer to concatenate distinct values. This can be especially useful for including a sparkline chart in a tabular report.
SELECT rtd.collectString(SET(CAST(COLLECT(<column name>) AS stringListType)), <delimeter>) from <table name>;
The following restrictions apply:
While concatenating NUMBER fields, explicitly cast the column to VARCHARs() as shown in the following example. While casting, we need to specify the size of the column.
Example:
SELECT rtd.collectString(SET(CAST(COLLECT(cast(client_id as varchar2(10))) AS stringListType)),', ') name from apt_v_job;
For STRING or NUMBER columns, this function is restricted to up to 512 characters.
Example:
SELECT rtd.collectString(SET(CAST(COLLECT(hostname) AS stringListType)),', ') name from apt_v_server;