원래 데이터
ID PID LOC NM ENM Lft Rgt
64 0 1 SG호텔(3G) SGHOTEL(3G) 84 81
76 0 1 R호텔(2G) RHotel(2G) 16 24
80 76 2 R호텔(진입_2G) RHotel(진입_2G) 40 45
82 76 3 R호텔(주차1_2G) RHotel(주차1_2G) 61 65
83 76 3 R호텔(주차2_2G) RHotel(주차2_2G) 73 77
만들 데이터
ID PID LOC NM ENM Lft Rgt
64 0 1 SG호텔(3G) SGHOTEL(3G) 84 81
76 76 2 R호텔(2G) RHotel(2G) 40 45
82 76 3 R호텔(주차1_2G) RHotel(주차1_2G) 61 65
83 76 3 R호텔(주차2_2G) RHotel(주차2_2G) 73 77
-------------------------
Loc가 2이면서 pid(parents id)가 id와 같은것은 부모의 id,name,ename 으로 변경하고
대신 부모 레코드는 제거하는 프로세스 입니다,
즉, 부모레코드대신 첫번째자식레코드(?)를 부모처럼 쓴다는 건데요,
며칠이것저것 해보다가, 아래처럼 해보았는데,
각각 만들어서 unoin all 하는방법 말고,
다른 방법은 없을까 싶어 질문합니다
-------------------------
WITH a AS
(
select '64'id,'0'pid,'1'loc,'SG호텔(3G)'nm,'SGHotel(3G)'enm,'84'lft,'81'rgt from dual
union all select '76'id,'0'pid,'1'loc,'R호텔(2G)'nm,'RHotel(2G)'enm,'16'lft,'24'rgt from dual
union all select '80'id,'76'pid,'2'loc,'R호텔(진입_2G)'nm,'RHotel(진입_2G)'enm,'40'lft,'45'rgt from dual
union all select '82'id,'76'pid,'3'loc,'R호텔(주차1_2G)'nm,'RHotel(주차1_2G)'enm,'61'lft,'65'rgt from dual
union all select '83'id,'76'pid,'3'loc,'R호텔(주차2_2G)'nm,'RHotel(주차2_2G)'enm,'73'lft,'77'rgt from dual
)
select a.*
from a
where a.loc='1' and not exists (select a.id from a b where a.id=b.pid)
union all
select b.id,a.pid,a.loc,b.nm,b.enm,a.lft,a.rgt
from a a, a b
where a.loc='2' and a.pid=b.id
union all
select a.*
from a
where a.loc='3'
;