|
[초기 DATA]
A 1
A 2
A 3
A 4
A 5
B 1
B 2
C 1
C 2
C 3
C 4
-- 결과 : 아래와 같은 결과를 나오게 처리
A 1 시작
A 2
A 3
A 4
A 5 종료
B 1 시작
B 2 종료
C 1 시작
C 2
C 3
C 4 종료
D 1 시작/종료
;
--방법 1
WITH T AS(
SELECT 'A' GB, 1 NO FROM DUAL UNION ALL
SELECT 'A' GB, 2 NO FROM DUAL UNION ALL
SELECT 'A' GB, 3 NO FROM DUAL UNION ALL
SELECT 'A' GB, 4 NO FROM DUAL UNION ALL
SELECT 'A' GB, 5 NO FROM DUAL UNION ALL
SELECT 'B' GB, 1 NO FROM DUAL UNION ALL
SELECT 'B' GB, 2 NO FROM DUAL UNION ALL
SELECT 'C' GB, 1 NO FROM DUAL UNION ALL
SELECT 'C' GB, 2 NO FROM DUAL UNION ALL
SELECT 'C' GB, 3 NO FROM DUAL UNION ALL
SELECT 'C' GB, 4 NO FROM DUAL UNION ALL
SELECT 'D' GB, 1 NO FROM DUAL
)
SELECT GB
, DECODE(GB, BEF_GB, '', '시작') ||
DECODE(GB, AFG_GB, '', DECODE(GB, BEF_GB, '', '/') || '종료')
FROM (
SELECT GB
, LAG(GB, 1) OVER(ORDER BY GB) BEF_GB
, LEAD(GB, 1) OVER(ORDER BY GB) AFG_GB
FROM T
) ;
--방법 2
WITH T AS(
SELECT 'A' GB, 1 NO FROM DUAL UNION ALL
SELECT 'A' GB, 2 NO FROM DUAL UNION ALL
SELECT 'A' GB, 3 NO FROM DUAL UNION ALL
SELECT 'A' GB, 4 NO FROM DUAL UNION ALL
SELECT 'A' GB, 5 NO FROM DUAL UNION ALL
SELECT 'B' GB, 1 NO FROM DUAL UNION ALL
SELECT 'B' GB, 2 NO FROM DUAL UNION ALL
SELECT 'C' GB, 1 NO FROM DUAL UNION ALL
SELECT 'C' GB, 2 NO FROM DUAL UNION ALL
SELECT 'C' GB, 3 NO FROM DUAL UNION ALL
SELECT 'C' GB, 4 NO FROM DUAL UNION ALL
SELECT 'D' GB, 1 NO FROM DUAL
)
SELECT GB, NO, SUBSTR( DECODE( NO, 1,'/시작' )||DECODE( NO, MAXS,'/종료' ), 2 ) STR
FROM (
SELECT GB, NO, MAX(NO) OVER( PARTITION BY GB ) MAXS
FROM T
); |
