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
The above requirement can also be achieved using UDF Group Concat in Netezza
Advertisements
