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

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-2453?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

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
                
> Add a string_agg or similarly named aggregate for creating a delimited concat
> -----------------------------------------------------------------------------
>
>                 Key: TEIID-2453
>                 URL: https://issues.jboss.org/browse/TEIID-2453
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Query Engine
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 8.4
>
>
> 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.

--
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