If you are trying to concatenate column values after grouping them in Netezza database, you would be searching for Netezza group_concat alternative function.
A Netezza group_concat is a UDF provided by the IBM. You have to take that C++ code and compile it on Netezza host to make use of that function. To compile the code you should be either nz user or Netezza admin. You have to compile the source code to new Netezza host in case if you migrate the data from one Netezza server to another.
Read:
- Netezza analytics functions
- IBM Netezza Split Delimited Fields into Table Records and Examples
- Netezza Date Functions and Examples
- IBM Netezza Date Formats and Examples
- Netezza String Functions and Examples
- Netezza LEFT and RIGHT Functions
Netezza Group_concat Alternative: Netezza XML functions
If you have Netezza SQL extension toolkit installed then you can perform the group concatenation using the set of XML functions available with Netezza extension toolkit.
Read: Download and Install Netezza SQL Extensions Toolkit
Netezza Group_concat Alternative Working Example
Below example shows you the group concatenation with the help of Netezza provided XML functions:
--Create Test Table to test Netezza group_concat alternative functionality using Netezza XML functions.
Create table concat_demo
(
Col1 varchar(10),
Col2 varchar(20)
);
--Insert values to Netezza table to test the concatenate the columns after grouping them.
insert into concat_demo values ('DB1','data1');
insert into concat_demo values ('DB1','data2');
insert into concat_demo values ('DB2','data3');
insert into concat_demo values ('DB3','data4');
insert into concat_demo values ('DB1','data5');
insert into concat_demo values ('DB1','data6');
insert into concat_demo values ('DB2','data7');
insert into concat_demo values ('DB3','data8');
insert into concat_demo values ('DB1','data9');
insert into concat_demo values ('DB4','data10');
insert into concat_demo values ('DB4','data11');
insert into concat_demo values ('DB5','data12');
insert into concat_demo values ('DB6','data13');
insert into concat_demo values ('DB7','data14');
insert into concat_demo values ('DB5','data15');
insert into concat_demo values ('DB6','data16');
--Netezza Group_Concat alternative. Use Netezza SQL extension toolkit provided XML function to concatenate the Netezza column values after grouping them.
select Col1
, count(*) as NUM_OF_ROWS
, trim(trailing ',' from SETNZ..replace(SETNZ..replace (SETNZ..XMLserialize(SETNZ..XMLagg(SETNZ..XMLElement('X',col2))), '<X>','' ),'</X>' ,',' )) AS NZ_CONCAT_STRING
from
tbl_concat_demo
group by Col1
order by 1;