Tuesday, September 30, 2014

String aggregation

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


SELECT id,
 (RTRIM((MAX(CASE WHEN RNO=1 THEN STRING_NAME ELSE '' END) ||','||
MAX(CASE WHEN RNO=2 THEN  coalesce(STRING_NAME,'') else '' END)||','||
MAX(CASE WHEN RNO=3 THEN  coalesce(STRING_NAME,'')else '' END)||','||
MAX(CASE WHEN RNO=4 THEN  coalesce(STRING_NAME,'')else '' END)||','||
MAX(CASE WHEN RNO=5 THEN  coalesce(STRING_NAME,'')else '' END)||','||
MAX(CASE WHEN RNO=6 THEN  coalesce(STRING_NAME,'')else '' END)||','||
MAX(CASE WHEN RNO=7 THEN  coalesce(STRING_NAME,'')else '' END)||','||
MAX(CASE WHEN RNO=8 THEN  coalesce(STRING_NAME,'')else '' END)||','||
MAX(CASE WHEN RNO=9 THEN  coalesce(STRING_NAME,'')else '' END)||','||
MAX(CASE WHEN RNO=10 THEN  coalesce(STRING_NAME,'')else '' END)),',')) AS REG_CD
FROM
(SELECT ID,STRING_NAME,
ROW_NUMBER) OVER
(PARTITION BY ID ORDER BY STRING_NAME) AS RNO
FROM YOUR_TABLE) A GROUP BY A.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

No comments :