Two oracle transpose Methods

Two oracle transpose Methods: Method 1: SYS_CONNECT_BY_PATH, ROW_NUMBER () OVER (partition .. order ..), start with, connect by prior Combination Method 2: wmsys. wm_concat example: In table 1, one col1 corresponds to multiple col2 fields. In the following example, we need to convert col2 to a format such as 2 3 4 where the col2 field value is 2 3 4 and it needs to be changed to 2-3-4, in addition, the value of col2 cannot be enumerated by thousands or tens of thousands. In this way, some other transpose methods through decode cannot implement the select tt method 1 SQL code. col1, '-' | ':' | TO_CHAR (SUBSTR (MAX (SYS_CONNECT_BY_PATH (TT. col2, '-'), 2) M -- to intercept the "-" FROM (select t. col1, T. col2, T. col1 + ROW_NUMBER () OVER (partition by t. col1 order by t. col2) RN, ROW_NUMBER () OVER (partition by t. col1 order by t. col2) RM -- the preceding two rows use two ROW_NUMBER () times because col1 is the cumulative value, so a T. col1 + ROW_NUMBER () is used to distinguish different groups. ROW_NUMBER () is used to set the start value of recursion, however, this value is "1" for different groups, so we need to use two FROM table1 t where/* T. col1 = TO_NUMBER ('000000') */) TT -- note the start with rm = 1 connect by prior rn + 1 = rn group by tt. col1; Use method 2 SQL code select substr (tt. co, 1, length (tt. co)-1), -- end with "-" tt. col1 from (select t. col1, replace (wmsys. wm_concat (t. col2 | '-'), ',', null) co -- remove "," from table1 t -- where t. col1 = TO_NUMBER ('123') group by t. col1) tt; the first one was previously written, and the second one was actually simpler.