how to denormalize data in teiid

[krishnam503]

Hi,

Is there any way in teiid i can apply below transformation..

My view returns below set of rows..

FUND     COUNTRY

123          US

123          UK

345          US

345          UK

345          IE

I wanted to get output like below

FUND     COUNTRY

123          US, UK

345          US,UK,IE

basically need comma separated list of countries for each fund.

[shawkins]

The simplest with Teiid 8.4 or later:

select fund, cast(string_agg(country, ',') as string) from ... group by fund

There are other ways to aggregate the values, such as creating xml, an array value, json, etc.

Steve