Concat SQL
How do you refine out to a certain format in a SQL string.
From my SQL code:
with ordemp as (select * from s_org_hierarchy order by ORGANIZATION_ID),
seqemp as (select rownum rn, ordemp.* from ordemp),
startfinish as (select min(rn) low, max(rn) high, ORGANIZATION_ID
from seqemp ee group by ORGANIZATION_ID),
paths as (
select sys_connect_by_path(description,'->') concat, ORGANIZATION_ID,
rn
from seqemp o connect by PARENT_ORGANIZATION_ID = prior
ORGANIZATION_ID
start with rn in (select low from startfinish s where
s.ORGANIZATION_ID = o.ORGANIZATION_ID)
) select ORGANIZATION_ID, concat from paths p where rn in
(select high from startfinish s where s.ORGANIZATION_ID =
p.ORGANIZATION_ID) order by ORGANIZATION_ID;
I am getting:
1 ->Org1
2 ->Org12
2 ->Org1->Org12
3 ->Org13
3 ->Org1->Org13
4 ->Org4
5 ->Org45
5 ->Org4->Org45
But I want:
1 Org1
2 Org1->Org12
3 Org1->Org13
4 Org4
5 Org4->Org45
Do I use SUBSTR or how would one concat for that output?
Thanks.