Thursday, March 1, 2012

Group by aggregation of a string column using connect by and partition by - different approach

SELECT * FROM TEST001

Result:

RULE_ID SEQ_NUM EXPR
-----------------------------
GROUP_001 1 (
GROUP_001 2 GP
GROUP_001 3 +
GROUP_001 4 GM
GROUP_001 5 )
GROUP_002 1 (
GROUP_002 2 GP
GROUP_002 3 +
GROUP_002 4 GM
GROUP_002 5 )

SELECT
RULE_ID,
REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(EXPR, ' '),2),' ','') EXPR
FROM
(
SELECT
EXPR,
RULE_ID,
COUNT(*) OVER ( PARTITION BY RULE_ID ) CNT,
ROW_NUMBER () OVER ( PARTITION BY RULE_ID ORDER BY SEQ_NUM) seq
FROM
TEST001
WHERE
RULE_ID IS NOT NULL
)
WHERE
SEQ=CNT
START WITH
SEQ=1
CONNECT BY PRIOR
SEQ+1=SEQ
and prior
RULE_ID=RULE_ID;

Result:
RULE_ID EXPR
-----------------------
GROUP_001 (GP+GM)
GROUP_002 (GP+GM)

No comments: