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;
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 :
Post a Comment