SELECT GI,MAX(SYS_CONNECT_BY_PATH(VAL,'/')) VAL,MAX(CNT) CNT
FROM (SELECT GI,VAL,ROW_NUMBER() OVER(PARTITION BY GI ORDER BY ROWNUM) RN,COUNT(*) OVER (PARTITION BY GI) CNT
FROM 테이블명)
START WITH RN=1
CONNECT BY PRIOR RN=RN-1
GROUP BY GI

이런씩으로 하시면 될듯하네요

쪼렙입니다.

WITH T AS (
SELECT 'A' AS CODE, '10' AS VAL FROM DUAL
UNION ALL SELECT 'B' AS CODE, '5' AS VAL FROM DUAL
UNION ALL SELECT 'C' AS CODE, '4' AS VAL FROM DUAL
UNION ALL SELECT 'B' AS CODE, '5' AS VAL FROM DUAL
UNION ALL SELECT 'C' AS CODE, '2' AS VAL FROM DUAL
UNION ALL SELECT 'B' AS CODE, '8' AS VAL FROM DUAL
-- UNION ALL SELECT 'B' AS CODE, '5' AS VAL FROM DUAL
)

SELECT CODE, SUBSTR(MAX(SYS_CONNECT_BY_PATH(VAL,'|')),2) AS VAL, MAX(RN)
FROM (
SELECT CODE
, VAL
, ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CODE) AS RN
FROM T
)
START WITH RN = 1
CONNECT BY PRIOR RN = RN -1
GROUP BY CODE
--GROUP BY CODE , VAL

WITH t AS
(
SELECT 'A' code, 10 val FROM dual
UNION ALL SELECT 'B', 5 FROM dual
UNION ALL SELECT 'C', 4 FROM dual
UNION ALL SELECT 'B', 5 FROM dual
UNION ALL SELECT 'C', 2 FROM dual
UNION ALL SELECT 'B', 8 FROM dual
)
, tt AS
(
SELECT code, val
, ROW_NUMBER() OVER(PARTITION BY code ORDER BY 1) rn
FROM t
)
SELECT code
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(val,'|')),2) vals
, COUNT(*) cnt
FROM tt
START WITH rn = 1
CONNECT BY PRIOR code = code
AND PRIOR rn = rn - 1
GROUP BY code
;

3분 모두 답변 정말 감사합니다~