Issue: Oracle Listagg connection string; Result: Method of Oracle Connection string

Methods for Oracle Connection strings

Method One: Wmsys.wm_concat (column)

Description: Its function was introduced in Oracle 10g, in the 10g version, the string type is returned, and the CLOB type is returned in the 11g version. The arguments inside the parentheses are columns, and can be a collection of multiple columns, meaning that the ' | | ' is freely available in parentheses Merges a string. As in the following example:

Select u_id, wmsys.wm_concat (Goods | | ' (' | | | num | | ' kg ') Goods_sum from shopping group by u_id

Method Two: Listagg (column,[,]) within group (order by) [Over (partition by)]

Description: Its function is introduced in Oracle 11g version, and the grouped data is concatenated by a certain sort of string. Where, "[,]" represents the delimiter of a string connection, and if you choose to use [Over (partition by)], it turns it into an analytic function;

Method Three: Sys_connect_by_path (column,< separator >)

Description: A string that is used to merge links with functions introduced in the Oracle 9i release. Note that it must be combined with the Connect by clause! The first parameter is a field that forms a tree, and the second parameter is the delimiter used by the parent and its children to separate the display.

Here are some examples of the above methods (running correctly in Oracle 11g version):

Instance:

method One: With Listagg (, ', ') within group ()

SQL Code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25

With Temp1 as ( Select ' A ' as username,1 as deptid from dual union ALL Select ' B ', 1 from dual UNION ALL Select ' C ', 1 from dual UNION ALL Select ' d ', 2 from dual UNION ALL Select ' E ', 2 from dual ), Temp2 as ( Select 1 as DeptID, ' Department 1 ' as deptname from dual union ALL Select 2, ' Department 2 ' from dual ) Select P.deptid, Listagg (T.username, ', ') within group (order by T.username) as username, P.deptname From Temp1 T,TEMP2 p where T.deptid=p.deptid GROUP BY P.deptid,p.deptname ORDER BY P.deptid ------------------------- DeptID UserName Deptname 1 A,b,c Department 1 2 D,e Department 2

Method Two: With Wm_concat ()

SQLcode

1 2 P align= "left" > 3 4 5 6 7 8 9

Select P.deptid, wm_concat (t.username) asusername, p.deptname from Temp1 t,temp2p where T.deptid=p.deptid Group by P.deptid,p.deptname order by P.deptid

method Three: Use Connect by

SQLcode

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Select DeptID, LTrim (Max (sys_ Connect_by_path (username, ', ')), ', '), deptname from ( Select P.deptid,t.username, P.deptname, row_number () over (partition Byt.deptid order by T.username As Ar from Temp1 t,temp2p where T.deptid=p.deptid ) start with ar=1 Connect by prior ar=ar-1 and Username=prior Username GROUP by Deptid,deptname ORDER by DeptID

--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------

--------------If there are errors, please advise!

--------------Technical Exchange qq:1732035211

-------------Technical Exchange e-mail:[email protected]

Issue: Oracle Listagg connection string; Result: Method of Oracle Connection string