Hi Tom,
I am new here, so if i ask wrong ques or not following the guidelines, apologizes.
Suppose i have a table like this:
COL1
----
A
B
C
D
E
F
G
I have to transform this columns in a single cell like this A,B,C,D,E,F,G. I know that can be done by listagg function but one of my friend said it can be done through connect by also.
Could you please explain how it can be done by connect by?
I tried to get this through this:
select col2
from (select level,substr(sys_connect_by_path( col1, ',' ),2) col2
from t
where level=7
connect by level<=7
order by col1)
where substr(col2,1,1)='A'
and
substr(col2,3,1)='B'
and
substr(col2,5,1)='C'
and
substr(col2,7,1)='D'
and
substr(col2,9,1)='E'
and
substr(col2,11,1)='F'
and
substr(col2,13,1)='G';But i dont think its a good practice. It could be done in better way(if possible)
When asking questions, please provide your example data in the form of:
- create table
- insert into table
This reduces the chances of us making a mistake or incorrect assumption when answering your question.
Anyway:
Doing:
where level=7
connect by level<=7
Generates all possibilities for each letter in each of the seven positions. That 7 ^ 7 = 823,543 rows!
The substrs then search through those to find the one combination that has all the letters in the right place.
That's a colossal amount of wasted effort.
It's better to generate the one row you want in the first place. You can do this by:
- State the beginning of the tree in the start with clause. That's the 'A'
- Use connect by prior to link the next row in the chain
Because you have consecutive letters, you can do this by converting them to their ASCII representation. And finding the one where the previous is one less than the current. e.g.:
prior ascii(l) = ascii(l) - 1
All you need to do then is return the leaf node at the end. The pseudocolumn connect_by_isleaf returns 1 for those that are (zero otherwise).
Put it all together and you get:
with rws as (
select chr(rownum+64) l from dual
connect by level <= 7
), tree as (
select substr ( sys_connect_by_path ( l , ',' ) , 2 ),
connect_by_isleaf lf
from rws
start with l = 'A'
connect by prior ascii(l) = ascii(l) - 1
)
select * from tree
where lf = 1;
SUBSTR(SYS_CONNECT_BY_PATH(L,','),2) LF
A,B,C,D,E,F,G 1