There are two options for the group_concat function in Netezza, cpp as-is version or the LUA version available in INZA.
Recommendation: add group_concat function to the SQL Extensions toolkit database, see more here: https://www.ibm.com/docs/en/netezza?topic=analytics-sql-extensions-toolkit
Note: NPSaaS on AWS or Azure, SQLEXT is installed for you.
Option 1: LUA (Netezza Performance Server on Cloud Pak for Data System)
export PATH=$PATH:/nz/extensions/nz/nzlua/bin
export NZ_DATABASE=SQLEXT
cd /nz/extensions/nz/nzlua/examples
nzl -d SQLEXT group_concat.nzl
nzsql -db SQLEXT
nzsql -db SQLEXT "grant execute on group_concat(varchar(128)) to public;"
Note: you can change the database if desired and change the grant to specific users or groups.
Option 2: AS-IS (not supported) cpp version of group_concat
Download the file to your Netezza system and extract it as the nz
user:
Run the install script:
tar xvf group_concat.tar
export NZ_DATABASE=SQLEXT
chmod +x install
./install
Test the group_concat
function:
nzsql sqlext -c "create table test_concat (c1 int, c2 varchar(10))"
nzsql sqlext -c "insert into test_concat values(1, 'a')"
nzsql sqlext -c "insert into test_concat values(1, 'b')"
nzsql sqlext -c "insert into test_concat values(1, 'c')"
nzsql sqlext -c "insert into test_concat values(2, 'x')"
nzsql sqlext -c "insert into test_concat values(2, 'y')"
nzsql sqlext -c "insert into test_concat values(2, 'z')"
nzsql sqlext -c "select c1, group_concat(c2) from test_concat group by 1"