Add strings in a column of different Oracle rows to remove duplicate values

As shown in the following table: srs_ B _cw_test
City people make
Guangzhou 1
Guangzhou 2 B
Guangzhou 3 C
Shanghai 4
Shanghai 5 e
Guangzhou 6
Shanghai 7 E

Achieve the following results
City people make
Shanghai 16 AE
Guangzhou 12 ABC

We will learn it step by step

(1) If the string is directly group by, only the number sum is allowed, and the string cannot be added.
Select city, sum (people) as people from srs_ B _cw_test group by city order by city
The implementation result is as follows:
City people
Shanghai 16
Guangzhou 12

(2) The following statement is incorrect. The make Column cannot be added.
Select city, sum (people) as people, make from srs_ B _cw_test group by city order by city

(3) If you write the following SQL statement
Select city, sum (people) as people from srs_ B _cw_test group by city, make order by city
The implementation result is as follows:
City people
Shanghai 4
Shanghai 12
Guangzhou 7
Guangzhou 3
Guangzhou 2

(4) Add the make column.
Select city, sum (people) as people, make from srs_ B _cw_test group by city, make order by city
The implementation result is as follows:
City people make
Shanghai 4
Shanghai 12 E
Guangzhou 7
Guangzhou 3 B
Guangzhou 2 C

(5) Next, we will try to add strings in the make column.

(6) construct a tree. The SQL statement is as follows:
Select city, people, make,
Row_number () over (order by city) Rn,
Row_number () over (partition by city order by city) Rm
From srs_ B _cw_test
The implementation result is as follows:
City people make rn RM
Shanghai 4 A 1 1
Shanghai 5 E 2 2
Shanghai 7 E 3 3
Guangzhou 6 A 4 1
Guangzhou 3 C 5 2
Guangzhou 2 B 6 3
Guangzhou 1 A 7 4

(7) with the tree, you can use the tree function sys_connect_by_path

(8)! If a string is concatenated by a tree constructed according to (6), an SQL statement is written first.
Select city, people, sys_connect_by_path (make, ',') as make
From (
Select city, people, make,
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
The implementation result is as follows:
City people make
Shanghai 4,
Shanghai 5, A, E
Shanghai 7, A, E, E
Guangzhou 6, A, E, E,
Guangzhou 3, A, E, E, A, C
Guangzhou 2, A, E, E, A, C, B
Guangzhou 1, A, E, E, A, C, B,
Guangzhou 6,
Only 7 pieces of data, how more than 1, the original is not good RN, different groups of numbers must be 'disconnected', resulting in connect by prior Rn = RN-1 processing problems

(9) Looking for Oracle analysis functions, we found a function useful to us rank (), so we modified 6 SQL statements.
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
The implementation result is as follows:
City people make rn RM
Shanghai 4 A 2 1
Shanghai 5 E 3 2
Shanghai 7 E 4 3
Guangzhou 6 A 8 1
Guangzhou 3 C 9 2
Guangzhou 2 B 10 3
Guangzhou 1 A 11 4
In this way, 'disconnections '.

(10) rewrite (8) SQL statements
Select city, people, 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
The implementation result is as follows:
City people make
Shanghai 4,
Shanghai 5, A, E
Shanghai 7, A, E, E
Guangzhou 6,
Guangzhou 3, a, c
Guangzhou 2, A, C, B
Guangzhou 1, A, C, B,

(11) The goal is almost achieved. The sum is used below, and the max function is used to retrieve the maximum value.
Select city, sum (people) as people, 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
The implementation result is as follows:
City people make
Shanghai 16, A, E, E
Guangzhou 12, A, C, B,

(12) place the first comma and use the ltrim Function
Select city, sum (people) as people, 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
The implementation result is as follows:
City people make
Shanghai 16 A, E, E
Guangzhou 12 A, C, B,

(13) Remove duplicate English letters. The power of the function is needed here.

(14) Write a function by yourself
/*************************************** **************************************** *****
Created by Zeng Hao
Creation Time:
Latest modifier: Zeng Hao
Last modified:
Purpose: Improve the split function,
Achieve this effect
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;

(15) call this function
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
The implementation result is as follows:
City people make
Shanghai 16 A, E
Guangzhou 12 A, C, B

(16) Success!