Oracle string addition Function

Http://lwl0606.cmszs.com/archives/oracle-string-add-function.html

Add the DSPNAME values of the same Account as the following table.
Account DSPNAME
L1 aa
L1 bb
L2 cc
Result L1 aa, bb

L2 cc

Method 1: Use cursor in the function

 

Create or replace function sda. FNROLE (ACC in varchar2)
Return varchar2 is
Result varchar2 (500 );
Cursor rad_cursor is
Select DSPNAME from SDA_USER_ROLE
Where LOWER (Account) = LOWER (ACC );
Begin
For rad_val in rad_cursor
Loop
Result: = Result | ',' | rad_val.DSPNAME;
End loop;
RESULT: = SUBSTR (Result, 2 );
Return (Result );
End FNROLE;

 

Use select sda. fnrole ('1') as a from dual;

Method 2: Use the oracle analysis function to convert it from miclu

City People Make
Guangzhou 1
Guangzhou 2 B
Guangzhou 3 C
Shanghai 4
Shanghai 5 E
Guangzhou 6
Shanghai 7 E

Select City, sum (People) as People, ZH_SPLIT (ltrim (max (sys_connect_by_path (Make, ',') as Make
From (
Select City, People, Make,
Rank () over (order by City) + row_number () over (order by City) RN,
Row_number () over (partition by City order by City) RM
From SRS_ B _CW_TEST
)
Start with RM = 1
Connect by prior RN = RN-1
Group
City

Write a function to remove duplicate values
/*************************************** **************************************** *****
Created by Zeng Hao
Creation Time:
Latest modifier: Zeng Hao
Last modified:
Purpose: Improve the split function,
Enter the string 123,123,234,345,234,345,456 and string,
123,234,345,456 output
**************************************** **************************************** ****/
Create or replace function ZH_SPLIT (v_string in varchar2, v_delimiter in varchar2)
Return varchar2
Is
J int: = 0;
I int: = 1;
Len_string int: = 0;
Len_delimiter int: = 0;
Str varchar2 (4000 );
V_return varchar2 (4000 );
Begin
Len_string: = LENGTH (v_string );
Len_delimiter: = LENGTH (v_delimiter );
While j <len_string
Loop
J: = INSTR (v_string, v_delimiter, I );
If j = 0 then
J: = len_string;
Str: = SUBSTR (v_string, I );
If instr (v_return, str)> 0 then
Null;
Else
V_return: = v_return | str | ',';
End if;
If I> = len_string then
Exit;
End if;
Else
Str: = SUBSTR (v_string, I, j-I );
I: = j + len_delimiter;
If instr (v_return, str)> 0 then
Null;
Else
V_return: = v_return | str | ',';
End if;
End if;
End loop;
V_return: = substr (v_return, 1, length (v_return)-1 );
Return v_return;
End;

Method 3: If the ID column is a number, you can remove repeated values.

Create table TEST
(Select 1 ID, '000000' MC from dual
Union
Select 1, '20180101' from dual
Union
Select 2, '20180101' from dual
Union
Select 2, '20180101' from dual
Union
Select 3, '20180101' from dual
Union
Select 3, '20180101' from dual
Union
Select 3, '20180101' from dual );

 

Select id, ltrim (max (sys_connect_by_path (mc, ','), ',') row2col
From (select id, mc,
ID + (row_number () over (order by id) node_id,
Row_number () over (partition by id order by id) rn
From test)
Start with rn = 1
Connect by node_id-1 = prior node_id
Group by id
Order by id;

Http://lwl0606.cmszs.com/archives/oracle-string-add-function.html