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
wantmannu
Messages: 39
Registered: June 2009
Location: cali
Member

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 messageGo to next message
Michel Cadot
Messages: 66641
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 messageGo to next message
Michel Cadot
Messages: 66641
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 #677340 is a reply to message #677321] Thu, 12 September 2019 09:59 Go to previous messageGo to next message
wantmannu
Messages: 39
Registered: June 2009
Location: cali
Member

Thanks Michael. First one works like a charm. Second one with listagg, my version is old I guess.
Appreciate it!!
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
Michel Cadot
Messages: 66641
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.


Previous Topic: what is utc and gmt
Next Topic: data masking
Goto Forum:
  


Current Time: Wed Oct 16 12:06:53 CDT 2019