oracle sys_connect_by_path function result set join

I have seen someone transform before, at that time just marveled 1, and then passed, did not write down, until the use of it, began to look everywhere, looking for all can not find traces, the heart is also depressed ah.
Today, I inadvertently looked at the use of connect by, and saw the usage of sys_connect_by_path, which gave me a surprise. sys_connect_by_path(columnname, seperator) can also be string, but the function itself is not used for us to do the result set connection, but to construct the tree path, so it needs to be used with connect by1.
Hehe heh, this is a bit of an overstatement, based on some of the functions of oracle 1, see how I can force a common table or result set without tree structure to make what we want.
magic is start.
Prop, 1 ordinary table, on 1 field name, let's call the table named test_sysconnectbypath, table name is too long, hee hee, not afraid, alias.
The following is the table data

NAME
------------------
shenzhen
wuhan
Shanghai
Beijing
tianjin
Singapore

The alias
SQL > with temp as (select name form test_sysconnectbypath);
This is an alias, and our sql statement below can replace the result set with temp. Of course this () can be the result set of your own complex query
First denatured, let's make this into a tree
How to transform into a tree structure, you immediately think, add 1 pid, and id to yo, there is no, let's add them. However, if you add id, how can you fill in their structure data? Here you need another function to show that lag(), lag() is the previous record, as opposed to lead. If it is a simple spelling, the tree structure is, the previous record is the parent node of the next record
So we use rownum, don't we just... The OK
action
select t.name, no, lag(no) over(order by no) pid from (select temp.*, rownum no from temp) t;
And there it is

NAME NO PID
-------------------- ---------- ----------

Shenzhen 1
Wuhan 2 1
Shanghai 3 2
Beijing 4 3
Tianjin 5 4
Singapore 6 5

So now it's a tree.
Again, the tree
action
select * from (select t.name, no, lag(no) over(order by no) pid from (select temp.*, rownum no from temp)) t start with pid is null connect by prior no=pid;
Look at the results

And there it is
NAME NO PID
-------------------- ---------- ----------
Shenzhen 1
Wuhan 2 1
Shanghai 3 2
Beijing 4 3
Tianjin 5 4
Singapore 6 5

It's weird that the result hasn't changed. Yes, it's just the tree that's being selected, so if you add lpad(", 4*level, "*')||name you can see the pattern
And then the last one goes into a string
select sys_connect_by_path(name. ',') text from (select t.name, no, lag(no) over(order by no) pid from (select temp.*, rownum no from temp)) t start with pid is null connect by prior no=pid;
See for yourselves.
Text
--------------------------------------------------

Shenzhen, Wuhan, Shanghai, Beijing, Tianjin, Singapore

......
Hehe heh, although it is done, but as said above, here is just a different kind of joy, because this is not the solution I saw before, but through this method, useful to the powerful connect by has analyzed the function over, just a sneer,
The search for work will go on. When will I find you out of the clouds?