Oracle Sys_connect_by_path function result set connection _oracle

Had seen someone converted, at that time just marvel, then passed, did not write down, until the use of the time, began to look everywhere, looking for to find no traces of the heart is also unhappy ah.
Today unintentionally, see connect by use, see the usage of Sys_connect_by_path, give me an alternative surprise, Sys_connect_by_path (ColumnName, seperator) can also spell a string, However, the function itself is not used to make this result set connection, but to construct the tree path, so it needs to be used with connect by.
Oh ah, here arrogant a, based on the understanding of some of the functions of Oracle on the basis of how I have been abruptly put a no tree structure of the ordinary table or result set to do what we want to.
Magic is start.
Props, a normal table, on a field name, let's call the table named Test_sysconnectbypath Bar, table name is too long, hee, not afraid, alias.
The following is the table data

NAME
------------------
Shenzhen
Wuhan
Shanghai
Beijing
Tianjin
Singapore

Alias of
Sql>with temp as (select Name Form Test_sysconnectbypath);
This is the alias, and our following SQL statement can replace the result set with temp. Of course, this () can be the result set of your own complex query.
First degeneration begins, turning this into a tree-shaped structure
How to become a tree structure, we immediately think, add a PID, and id just do yo, here is not, we will add to them. However, with the ID added, how to fill in their structure data, here need another function to lag (), lag () is a record, and the lead, if it is simple spelling, the tree structure is not, the last record is the next record of the parent node?
So we use rownum, not just .... Okay, OK.
Action
Select T.name, no, lag (NO) over (order by NO) PID from (select Temp.*, rownum no from temp) t;
The results came out.

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

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

It's a tree now.
and change the tree.
Action
SELECT * FROM (select T.name, No, lag (no) on PID from (select Temp.*, rownum No to temp)) t start with pi D is null connect by prior no=pid;
Look at the results.

The results came out.
NAME NO PID
-------------------- ---------- ----------
Shenzhen 1
Wuhan 2 1
Shanghai 3 2
Beijing 4 3
Tianjin 5 4
Singapore 6 5

Strange results have not changed yo, yes, here is just to select the tree, if you add a Lpad (", 4*level, ' *") | | Name can be seen on the horizon.
One last change, 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 to temp)) t start with P ID is null connect by prior no=pid;
You can see the results for yourselves.
Text
--------------------------------------------------

Shenzhen, Wuhan, Shanghai, Beijing, Tianjin, Singapore

......
Oh ah, although it is to make to come, but as mentioned above, here is just another joy, because this is not the solution I have seen before, but through this method, useful to the powerful connect by the analysis function over, is only a joy,
Looking for a job to continue, when it will be very good to me out of the clouds to find you yo.