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.

Background:

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.

Problem:

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

Solution Approach:

Approach 1:

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:

1. Install this UDA to each single database to call it directly.
2. 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

Approach 2:

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.