GUBUN은 항상 10 , 20 고정인가요?

-- MS-SQL이 구버젼이라 DENCE_RANK() 는 테스트 안됨.. ㅡㅡ;
WITH t AS
(
SELECT 283 T1, '20130210' FIST_DT, 1 SEQ_NO, 2 SEQ_NEXT_NO, '20170725' STAR_DT, '20170725' END_DT, '20170907' AD_DT, '10' GUBUN
UNION ALL SELECT 283 T1, '20130210' FIST_DT, 1 SEQ_NO, 3 SEQ_NEXT_NO, '20170726' STAR_DT, '20170726' END_DT, '20170907' AD_DT, '10' GUBUN
UNION ALL SELECT 283 T1, '20130210' FIST_DT, 1 SEQ_NO, 4 SEQ_NEXT_NO, '20170808' STAR_DT, '20170808' END_DT, '20170907' AD_DT, '10' GUBUN
UNION ALL SELECT 283 T1, '20130210' FIST_DT, 1 SEQ_NO, 6 SEQ_NEXT_NO, '20171015' STAR_DT, '20180114' END_DT, '20170907' AD_DT, '10' GUBUN
UNION ALL SELECT 283 T1, '20130210' FIST_DT, 1 SEQ_NO, 12 SEQ_NEXT_NO, '20171104' STAR_DT, '20180114' END_DT, '20171107' AD_DT, '10' GUBUN
UNION ALL SELECT 283 T1, '20130210' FIST_DT, 1 SEQ_NO, 5 SEQ_NEXT_NO, '20170815' STAR_DT, '20171014' END_DT, '20170909' AD_DT, '20' GUBUN
UNION ALL SELECT 283 T1, '20130210' FIST_DT, 1 SEQ_NO, 13 SEQ_NEXT_NO, '20171106' STAR_DT, '20171204' END_DT, '20071115' AD_DT, '20' GUBUN
) , T2 AS (
SELECT 1 lv
 UNION ALL
SELECT lv + 1 lv
  FROM T2
 WHERE lv + 1 <= 2
)

				 
SELECT T1
     , FIST_DT  
     , SEQ_NO
     , LV AS NEW_SEQ
     , MAX_STAR_DT AS AD_DT
     , CASE WHEN lv = 1 THEN STAR_DT_10_1 ELSE STAR_DT_10_2 END STAR_DT_10
     , CASE WHEN lv = 1 THEN END_DT_10_1  ELSE END_DT_10_2  END END_DT_10
     , CASE WHEN lv = 1 THEN STAR_DT_20_1 ELSE STAR_DT_20_2 END STAR_DT_20
     , CASE WHEN lv = 1 THEN END_DT_20_1  ELSE END_DT_20_2  END END_DT_20
  FROM (SELECT MAX(A.T1) T1 
			 , MAX(A.FIST_DT)     FIST_DT
			 , MAX(A.SEQ_NO)      SEQ_NO
			 , MAX(A.MAX_STAR_DT) MAX_STAR_DT
			 , MAX(CASE WHEN STAR_DT_MIN_SORT = 1 AND GUBUN = 10 THEN STAR_DT END ) STAR_DT_10_1
			 , MAX(CASE WHEN STAR_DT_MIN_SORT = 2 AND GUBUN = 10 THEN STAR_DT END ) STAR_DT_10_2
			 , MAX(CASE WHEN END_DT_MIN_SORT  = 1 AND GUBUN = 10 THEN END_DT  END ) END_DT_10_1
			 , MAX(CASE WHEN END_DT_MIN_SORT  = 2 AND GUBUN = 10 THEN END_DT  END ) END_DT_10_2
			 , MAX(CASE WHEN STAR_DT_MIN_SORT = 1 AND GUBUN = 20 THEN STAR_DT END ) STAR_DT_20_1
			 , MAX(CASE WHEN STAR_DT_MIN_SORT = 2 AND GUBUN = 20 THEN STAR_DT END ) STAR_DT_20_2
			 , MAX(CASE WHEN END_DT_MIN_SORT  = 1 AND GUBUN = 20 THEN END_DT  END ) END_DT_20_1
			 , MAX(CASE WHEN END_DT_MIN_SORT  = 2 AND GUBUN = 20 THEN END_DT  END ) END_DT_20_2	 
		  FROM (
				SELECT T1 
					 , FIST_DT
					 , SEQ_NO
					 , SEQ_NEXT_NO
					 , GUBUN  
					 , STAR_DT
					 , END_DT
					 , MAX(AD_DT) OVER() MAX_STAR_DT
					 , DENCE_RANK() OVER(PARTITION BY GUBUN ORDER BY STAR_DT ASC ) STAR_DT_MIN_SORT
					 , DENCE_RANK() OVER(PARTITION BY GUBUN ORDER BY END_DT DESC ) END_DT_MIN_SORT
				 FROM T
			   ) A 
		) AA
	  , T2 BB
  	

아니요 다른 코드 값도 있습니다

음...
제목은 오라클인데? WITH 구문은 FROM dual 이 없는걸로 보면 오리클이 아닌데요?
제목대로 오라클로 답변합니다.
 

WITH t AS
(
SELECT 283 t1, '20130210' fist_dt, 1 seq_no, 2 seq_next_no, '20170725' star_dt, '20170725' end_dt, '20170907' ad_dt, '10' gubun FROM dual
UNION ALL SELECT 283, '20130210', 1,  3, '20170726', '20170726', '20170907', '10' FROM dual
UNION ALL SELECT 283, '20130210', 1,  4, '20170808', '20170808', '20170907', '10' FROM dual
UNION ALL SELECT 283, '20130210', 1,  6, '20171015', '20180114', '20170907', '10' FROM dual
UNION ALL SELECT 283, '20130210', 1, 12, '20171104', '20180114', '20171107', '10' FROM dual
UNION ALL SELECT 283, '20130210', 1,  5, '20170815', '20171014', '20170909', '20' FROM dual
UNION ALL SELECT 283, '20130210', 1, 13, '20171106', '20171204', '20071115', '20' FROM dual
)
SELECT *
  FROM (SELECT t1, fist_dt, seq_no, gubun
             , new_seq
             , ad_dt
             , star_dt st_dt
             , CASE WHEN new_seq = 1 THEN end_dt ELSE end_dt_2 END ed_dt
          FROM (SELECT t1, fist_dt, seq_no, gubun
                     , star_dt, end_dt
                     , ROW_NUMBER() OVER(PARTITION BY t1, fist_dt, seq_no, gubun ORDER BY star_dt) new_seq
                     , MAX( ad_dt ) OVER(PARTITION BY t1, fist_dt, seq_no, gubun) ad_dt
                     , MAX(end_dt ) OVER(PARTITION BY t1, fist_dt, seq_no, gubun) end_dt_2
                  FROM t
                ) a
         WHERE new_seq <= 2
        ) a
 PIVOT (MIN(ad_dt) ad_dt, MIN(st_dt) st_dt, MIN(ed_dt) ed_dt
        FOR gubun IN (10, 20, 30)) a
;