Netezza: concatenate rows into string with XMLAGG()

Note: Same functionality can be achieved creating UDX at Netezza database:

Netezza: create UDX concatenating row values into coma delimited string GROUP_CONCAT

---

Pre-condition:  you should have SQL Toolkit installed (read installation notes here).  

SQL Extensions Toolkit: reference here.

--- Create sample data

create table t1 (num int, color varchar(10 ));

insert into t1 values (1,'red');

insert into t1 values (2,'black');

insert into t1 values (3,'red');

insert into t1 values (4,'yellow');

insert into t1 values (5,'green');

select * from t1 order by 1;

------------------------------------------

--- Simple example

select SQLX..replace (

                     SQLX.. replace(

                                  SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement( 'X',color)))

                     , '<X>','' )

              , '</X>',',' )

from t1;

------------------------------------------

--- Trim trailing comma

select trim (trailing ',' from

                  SQLX..replace(SQLX..replace (SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement('X',color))), '<X>','' ),'</X>' ,',' )

              )

from t1;

------------------------------------------

--- Experimenting with volume of data to concatenate

select database

       , count(* ) as NUM_OF_TABS

       , length(SQLX..replace (SQLX..replace(SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement( 'X',tablename))), '<X>','' ),'</X>' ,',' )) as STRING_LEN

       , trim(trailing ',' from SQLX..replace(SQLX..replace (SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement('X',tablename))), '<X>','' ),'</X>' ,',' )) AS CONCAT_STRING

from

       ( select * from _v_table_xdb order by random() limit 1400) as f

group by database ;

select * from _v_table_xdb order by random();

select random ();

Testing the limits of concatenated string: 

Renders "XML buffer overflow" when too much of data (in my case over 1600 records). 

------------------------------------------

/*

ERROR [HY000] ERROR:  0 : XML: Buffer Overflow

 */

---

/* Select user logon activity with list of databases ever accessed (last fiels)  */

SELECT case when (select count (* ) from _V_DSLICE) = 22 then 'DEV'

            when (select count (* ) from _V_DSLICE) = 46 then 'TEST'

            when (select count (* ) from _V_DSLICE) = 92 then 'PROD'

       else 'UNK' end as ENV,

        username,

        U.DISPLAYNAME,

        MAX(LAST_LOGON) as last_logon,

        SUM(ACCESS_COUNT) as total_login_count,

        trim (trailing ', ' from SQLX..replace(SQLX.. replace (SQLX..XMLserialize(SQLX..XMLagg(SQLX..XMLElement('X' ,dbname))), '<X>', '' ), '</X>' ,',' )) AS DATABASES

FROM

(

     select username, dbname, max( time) AS LAST_LOGON, count(*) AS ACCESS_COUNT

     from HISTDB.HIST."$v_hist_log_events"

     where username not in ('ADMIN4BACKUP','ADMIN' ,'S_NZ_PRODUSER' ,'S_NZ_PRODCOGNOS' ,'HIST' ,'S_NZ_PRODADMIN' ,'TESTSD' ,'TESTSAVO' )

     group by username, dbname

) as f

  JOIN NZDBA. ADMIN.V_ACTIVE_USERS U

ON U.UID = F.username

GROUP BY username, U.DISPLAYNAME

 order by total_login_count desc

 ;

---

Note, in Oracle same string concatenation functionality can be achieved in multiple ways. This post describes Oracle's way doing this.