netezza string aggregation

Posted: September 30, 2014 in Netezza
Sting aggregation in Netezza
 
Input data 
 
101 Kumar
101 Swami
 
102 John
102 D
 
103 David
103 R
 
104 jean morgan
 
required Out put 
 
101 Kumar Swami
102 John D
103 David R
104 Jean Morgan
 
Below Query can handle up to 10 repetitions per id (for example 101 ten repetitions
 
SELECTid,
 (RTRIM((MAX(CASEWHEN RNO=1THEN STRING_NAME ELSE END) ||‘,’||
MAX(CASEWHEN RNO=2THEN  coalesce(STRING_NAME,”) else END)||‘,’||
MAX(CASEWHEN RNO=3THEN  coalesce(STRING_NAME,”)else END)||‘,’||
MAX(CASEWHEN RNO=4THEN  coalesce(STRING_NAME,”)else END)||‘,’||
MAX(CASEWHEN RNO=5THEN  coalesce(STRING_NAME,”)else END)||‘,’||
MAX(CASEWHEN RNO=6THEN  coalesce(STRING_NAME,”)else END)||‘,’||
MAX(CASEWHEN RNO=7THEN  coalesce(STRING_NAME,”)else END)||‘,’||
MAX(CASEWHEN RNO=8THEN  coalesce(STRING_NAME,”)else END)||‘,’||
MAX(CASEWHEN RNO=9THEN  coalesce(STRING_NAME,”)else END)||‘,’||
MAX(CASEWHEN RNO=10THEN  coalesce(STRING_NAME,”)else END)),‘,’)) AS REG_CD
FROM
(SELECTID,STRING_NAME,
ROW_NUMBER) OVER
(PARTITIONBY ID ORDERBY STRING_NAME) ASRNO
FROMYOUR_TABLE) A GROUP BYA.ID

The above requirement can also be achieved using UDF Group Concat in Netezza

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s