[ https://issues.jboss.org/browse/TEIID-2453?page=com.atlassian.jira.plugin... ] Steven Hawkins edited comment on TEIID-2453 at 4/10/13 12:49 PM: ----------------------------------------------------------------- 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 to selectively 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 was (Author: shawkins): 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