Home » SQL & PL/SQL » SQL & PL/SQL » Sql query to group the values in comma seperated values (Oracle DB 10.2.0.2)
Sql query to group the values in comma seperated values [message #677318] |
Wed, 11 September 2019 22:49 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
wantmannu
Messages: 42 Registered: June 2009 Location: cali
|
Member |
![man.youu@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
I Have a table with some values shown below.
CREATE TABLE xx_report_email (
report_name VARCHAR2(20),
email VARCHAR2(100)
);
INSERT INTO xx_report_email VALUES (
'rep1',
'abc@gmail.com;xyz@gmail.com'
);
INSERT INTO xx_report_email VALUES (
'rep2',
'def@gmail.com;xyz@gmail.com'
);
INSERT INTO xx_report_email VALUES (
'rep3',
'def@gmail.com;abc@gmail.com'
);
Expecting the result to be in two columns like below.
result
email reports
abc@gmail.com rep1,rep3
xyz@gmail.com rep1,rep2
def@gmail.com rep2,rep3
I have achieved this in plsql but I am finding it difficult to achieve in sql query. Can you guys please help??
I am able to
|
|
|
Re: Sql query to group the values in comma seperated values [message #677320 is a reply to message #677318] |
Thu, 12 September 2019 01:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
10.2 is really old, many current features are not there.
Here's a way:
SQL> @v
Oracle version: 10.2.0.5.14 EE
SQL> col email format a30
SQL> col reports format a30
SQL> with
2 data as (select report_name, ';'||email||';' email from xx_report_email),
3 split as (
4 select report_name,
5 substr(email,
6 instr(email, ';', 1, column_value)+1,
7 instr(email, ';', 1, column_value+1)-instr(email, ';', 1, column_value)-1
8 ) email
9 from data,
10 table(cast(multiset(select level from dual
11 connect by level < length(email)-length(replace(email,';')))
12 as sys.odciNumberList))
13 )
14 select email, substr(sys_connect_by_path(report_name,','),2) reports
15 from split a
16 where connect_by_isleaf = 1
17 connect by prior email = email and prior report_name < report_name
18 start with report_name = (select min(report_name) from split b where b.email = a.email)
19 order by email
20 /
EMAIL REPORTS
------------------------------ ------------------------------
abc@gmail.com rep1,rep3
def@gmail.com rep2,rep3
xyz@gmail.com rep1,rep2
You can use T. Kyte's STRAGG function as suggested in your previous topic:
SQL> with
2 data as (select report_name, ';'||email||';' email from xx_report_email),
3 split as (
4 select report_name,
5 substr(email,
6 instr(email, ';', 1, column_value)+1,
7 instr(email, ';', 1, column_value+1)-instr(email, ';', 1, column_value)-1
8 ) email
9 from data,
10 table(cast(multiset(select level from dual
11 connect by level < length(email)-length(replace(email,';')))
12 as sys.odciNumberList))
13 )
14 select email, stragg(report_name) reports
15 from split
16 group by email
17 order by email
18 /
EMAIL REPORTS
------------------------------ ------------------------------
abc@gmail.com rep1,rep3
def@gmail.com rep2,rep3
xyz@gmail.com rep1,rep2
|
|
|
Re: Sql query to group the values in comma seperated values [message #677321 is a reply to message #677320] |
Thu, 12 September 2019 01:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
There is also the undocumented (and so not recommended) WM_CONCAT function:
SQL> with
2 data as (select report_name, ';'||email||';' email from xx_report_email),
3 split as (
4 select report_name,
5 substr(email,
6 instr(email, ';', 1, column_value)+1,
7 instr(email, ';', 1, column_value+1)-instr(email, ';', 1, column_value)-1
8 ) email
9 from data,
10 table(cast(multiset(select level from dual
11 connect by level < length(email)-length(replace(email,';')))
12 as sys.odciNumberList))
13 )
14 select email, WM_CONCAT(report_name) reports
15 from split
16 group by email
17 order by email
18 /
EMAIL REPORTS
------------------------------ ------------------------------
abc@gmail.com rep1,rep3
def@gmail.com rep2,rep3
xyz@gmail.com rep1,rep2
|
|
|
|
Re: Sql query to group the values in comma seperated values [message #677342 is a reply to message #677340] |
Thu, 12 September 2019 10:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Second one with listagg
The second one is with STRAGG, a custom aggregate function from Tom Kyte, not listagg.
The problem with the first one is that it will quickly run very very slow and consume resources (memory and cpu) when the number of rows will grow.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:08:18 CDT 2024
|