group_concat in netezza
Group_concat in netezza is sometimes required when you want to concatenate strings after grouping the columns values. Built-in Group_concat in netezza function is not available out of the box, though Netezza group_concat alternative working example is available installing group_concat UDA in Netezza.
Company Netezza (pronounced as Ne-Teez-a) was founded on 1999. Their primary objective was to design and market high performance data warehouse appliances and advanced analytics applications for enterprise level data warehousing and Business Intelligence (BI), predictive analytics and business continuity planning. Netezza was acquired by IBM on 2010. It is currently transforming to IBM performance server which is built on IBM Cloud Pak for Data System. IBM Performance Server is a cloud-native system of insight that is 100 percent compatible with existing Netezza and PureData System for Analytics appliances.
If your question is like below:
How can I concatenate strings after grouping them using Netezza sql?
Input - Col1 Col2 100 aa 100 bb 200 pp 200 qq 200 rr
Expected Output -
Col1 Col2 100 aa,bb 200 pp,qq,rr
Using C++ group_concat() UDA. Are wondering how to get and install group_concat UDA in Netezza?
You can download the source code from below site (you might need to login to the site): https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/W9363c60167c7_4e4b_a188_a8bad09b7861/page/C__%20UDX%20Examples
Once download is complete, extract group_concat.tgz file then unzip it and install .cpp file against the database you want to. To compile the code, you should be either nz user or Netezza admin.
You can use this Group_concat UDA either of the following ways:
- Install this UDA to each single database to call it directly.
- Install this UDA in the system only and then cross reference it to any other database.
The installation looks like below:
$ ./install yourdb CREATE AGGREGATE Created uda Done CREATE AGGREGATE Created uda Done
The usage would look like:
yourdb.ADMIN(ADMIN)=> select col1, group_concat(col2,',') from yourtest_gc group by col1 order by col1; COL1 | GROUP_CONCAT ------+-------------- 100 | aa,bb 200 | pp,qq,rr (2 rows)
You can find detailed answers in Group Concat in Netezza - IBM PureData-Enzee Community Forum
Using Netezza Group_concat Alternative: Netezza XML functions.
In this case, you need to install Netezza SQL extension toolkit installed. Then you can perform the group concatenation using the set of XML functions available with Netezza extension toolkit.
select Col1, count(*) as NUM_OF_ROWS, trim(trailing ',' from SETNZ..replace(SETNZ..replace (SETNZ..XMLserialize(SETNZ..XMLagg(SETNZ..XMLElement('X',col2))), '
','' ),'' ,',' )) AS NZ_CONCAT_STRING from yourtest_gc group by Col1 order by 1;
Here Netezza SQL: GROUP_CONCAT, REPLACE XML functions have been used.