[teiid-issues] [JBoss JIRA] (TEIID-2453) Add a string_agg or similarly named aggregate for creating a delimited concat

[ https://issues.jboss.org/browse/TEIID-2453?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-2453: ---------------------------------- Description: Similar to the pg string_agg (which supports blob/clob) delimited concat, we should offer a built-in aggregate. There is similar support on oracle 11g+ and mysql. (was: Similar to the pg string_agg (which supports blob/clob) delimited concat, we should offer a built-in aggregate.) Affects: Documentation (Ref Guide, User Guide, etc.),Release Notes (was: Documentation (Ref Guide, User Guide, etc.)) This may be bumped out of the 8.4 release as there are possible workarounds - If a trailing delimiter can be tolerated (and assuming string values), then use: SELECT XMLAGG(XMLPARSE(CONTENT col || ',' WELLFORMED)) FROM tbl XMLSERIALIZE can then be used to retrieve the result as a string (which may truncate), clob, or blob. If a trailing delimiter is not wanted, then the workaround becomes more complicated: SELECT XMLAGG(XMLPARSE(CONTENT (CASE WHEN rn = 1 THEN '' ELSE ',' END) || col WELLFORMED) ORDER BY rn) FROM (SELECT col, row_number() over (ORDER BY col NULLS LAST) as rn FROM tbl) X Here the window function provides a row number so that we can selective include the delimiter. If a group by clause is needed then the row_number must be computed relative to the grouping using partitioning: SELECT col2, XMLAGG(XMLPARSE(CONTENT (CASE WHEN rn = 1 THEN '' ELSE ',' END) || col WELLFORMED) ORDER BY rn) FROM (SELECT col, row_number() over (PARTITION BY col2 ORDER BY col NULLS LAST) as rn FROM tbl) X GROUP BY col2 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira