Archive for September, 2014

String aggregation

Posted: September 30, 2014 in Netezza

Netezza, Oracle and SQL server string aggregation examples.

String 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.

In Oracle:- 

SELECT ID, 
       Listagg(STRING_NAME, ‘,’) 
         within GROUP (ORDER BY ID) AS employees 
FROM   TABLE
GROUP  BY ID
; 

Using Oracle Unlisted function:- 

SELECT ID, 
       WM_CONCAT(STRING_NAME)  
FROM   TABLE
GROUP  BY ID
;

In SQL Server :-

SELECT  ID,
                ( Stuff((SELECT ‘,’ + STRING_NAME
                         FROM   TABLE E2 
                         WHERE  E1.ID= E2.ID
                         ORDER  BY STRING_NAME
                         FOR xml path(), type, 
                  root).value(‘root[1]’, ‘nvarchar(max)’), 1, 1, ) ) AS 
                STRING_NAME
FROM   TABLE E1 
ORDER  BY ID