I am struggling with some complex hierarchical data. I have successfully used a CONNECT BY query to limit the rows down to the subset that i want - and i have used SYS_CONNECT_BY_PATH to return the full tree up to the nodes of interest.
this gives me essentially some rows like this (delimited by '|'):
id path
-------------------
1, '|10|11|12|13'
2, '|10|14|15'
3, '|16|11|12|13'
4, '|16|17'
now - my challenge is to unwrap or UNPIVOT these values back into a structure like this:
id ord node
-------------
1, 1, 10
1, 2, 11
1, 3, 12
1, 4, 13
2, 1, 10
2, 2, 14
2, 3, 15
3, 1, 16
3, 2, 11
3, 3, 12
3, 4, 13
4, 1, 16
4, 2, 17
I think i am unable to use UNPIVOT directly as that is working on a fixed set of columns - which this is not.
I am playing with a PIPELINE function to unwrap this, but frankly - passing all these rows to the function is an issue since they come from another query. I am wondering if anyone has a way to UNPIVOT the values from a SYS_CONNECT_BY_PATH result set back into rows that is maybe a pure sql solution - probably with REGEX parsing...
help always appreciated - thanks
Yes, UNPIVOT operator wont do much here to help you produce the desired output.
As one of the approaches you could user regexp_count()(11g R1 version and up) regular
expression function to count all occurrences of numbers and then use regexp_substr() regular
expression function to extract the numbers as follows:
-- sample of data
SQL> with t1(id1, path1) as(
2 select 1, '|10|11|12|13' from dual union all
3 select 2, '|10|14|15' from dual union all
4 select 3, '|16|11|12|13' from dual union all
5 select 4, '|16|17' from dual
6 ),
7 occurrences(ocr) as( -- occurrences
8 select level
9 from ( select max(regexp_count(path1, '[^|]+')) as mx_ocr
10 from t1
11 ) t
12 connect by level <= t.mx_ocr
13 )
14 select id1
15 , row_number() over(partition by id1 order by id1) as ord
16 , node
17 from ( select q.id1
18 , regexp_substr(q.path1, '[^|]+', 1, o.ocr) as node
19 from t1 q
20 cross join occurrences o
21 )
22 where node is not null
23 order by id1, 2, node
24 ;
Result:
ID1 ORD NODE
---------- ---------- ------------------------------------------------
1 1 10
1 2 11
1 3 12
1 4 13
2 1 10
2 2 14
2 3 15
3 1 11
3 2 12
3 3 13
3 4 16
4 1 16
4 2 17
13 rows selected
As another approach, starting from 10g version and up, you could use model clause:
SQL> with t1(id1, path1) as(
2 select 1, '|10|11|12|13' from dual union all
3 select 2, '|10|14|15' from dual union all
4 select 3, '|16|11|12|13' from dual union all
5 select 4, '|16|17' from dual
6 )
7 select id1
8 , ord
9 , node
10 from t1
11 model
12 partition by ( rownum as id1)
13 dimension by ( 1 as ord)
14 measures( path1
15 , cast(null as varchar2(11)) as node
16 , nvl(regexp_count(path1, '[^|]+'), 0) as ocr )
17 rules(
18 node[for ord from 1 to ocr[1] increment 1] =
19 regexp_substr(path1[1], '[^|]+', 1, cv(ord))
20 )
21 order by id1, ord, node
22 ;
Result:
ID1 ORD NODE
---------- ---------- -----------
1 1 10
1 2 11
1 3 12
1 4 13
2 1 10
2 2 14
2 3 15
3 1 16
3 2 11
3 3 12
3 4 13
4 1 16
4 2 17
13 rows selected