Home » SQL & PL/SQL » SQL & PL/SQL » GROUP_CONCAT equivalent on oracle - help needed (Oracle 10G)
GROUP_CONCAT equivalent on oracle - help needed [message #528723] Wed, 26 October 2011 11:53 Go to next message
borg_jos
Messages: 17
Registered: March 2011
Junior Member
Hi, I have the following table and require to concatenate REFERENCE field where GROUP field is common. I am using SQL command on Oracle 10G.

Any help would be appreciated.

TABLE 1
GROUP	REFERENCE
A	SHOP 1
A	SHOP 2
B	SHOP 3
B	SHOP 4
C	SHOP 5


Result
GROUP	REFERENCE
A	SHOP 1, SHOP 2
B	SHOP 3, SHOP 4
C	SHOP 5



insert into TABLE 1 (GROUP,REFERENCE)
values ('A','SHOP 1');
commit;

insert into TABLE 1 (GROUP,REFERENCE)
values ('A','SHOP 2');
commit;

insert into TABLE 1 (GROUP,REFERENCE)
values ('B','SHOP 3');
commit;

insert into TABLE 1 (GROUP,REFERENCE)
values ('B','SHOP 4');
commit;

insert into TABLE 1 (GROUP,REFERENCE)
values ('C','SHOP 5');
commit;

Re: GROUP_CONCAT equivalent on oracle - help needed [message #528733 is a reply to message #528723] Wed, 26 October 2011 13:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2010
Registered: January 2010
Senior Member
Version? On 11.2:

SQL> column reference format a20
SQL> SELECT  *
  2    FROM  SAMPLE_TABLE
  3  /

G REFERENCE
- --------------------
A SHOP 1
A SHOP 2
B SHOP 3
B SHOP 4
C SHOP 5

SQL> SELECT  GRP,
  2          RTRIM(LISTAGG(REFERENCE || ', ') WITHIN GROUP(ORDER BY 1),', ') REFERENCE
  3    FROM  SAMPLE_TABLE
  4    GROUP BY GRP
  5  /

G REFERENCE
- --------------------
A SHOP 1, SHOP 2
B SHOP 3, SHOP 4
C SHOP 5

SQL> 


On 10g:

SQL> SELECT  GRP,
  2          RTRIM(XMLAGG(XMLELEMENT(R,REFERENCE,', ').EXTRACT('//text()')),', ') REFERENCE
  3    FROM  SAMPLE_TABLE
  4    GROUP BY GRP
  5  /

G REFERENCE
- --------------------
A SHOP 1, SHOP 2
B SHOP 3, SHOP 4
C SHOP 5

SQL> 


SY.
Re: GROUP_CONCAT equivalent on oracle - help needed [message #528773 is a reply to message #528723] Thu, 27 October 2011 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that your test case:
1/ misses CREATE TABLE statement
2/ contains only invalid INSERT statements

Please, test your test case before posting it.

Regards
Michel
Re: GROUP_CONCAT equivalent on oracle - help needed [message #528781 is a reply to message #528733] Thu, 27 October 2011 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Solomon,

I wonder if xml functions are automatically installed or if this is an option. Do you know the answer? (We, in our scripts, automatically run initxml.sql and xmlja.sql but I don't know if they are also called by the default database creation.)

Regards
Michel
Re: GROUP_CONCAT equivalent on oracle - help needed [message #529110 is a reply to message #528733] Fri, 28 October 2011 13:45 Go to previous messageGo to next message
lott42
Messages: 96
Registered: June 2010
Member
I like this technique

SQL> SELECT  GRP,
  2          RTRIM(XMLAGG(XMLELEMENT(R,REFERENCE,', ').EXTRACT('//text()')),', ') REFERENCE
  3    FROM  SAMPLE_TABLE
  4    GROUP BY GRP
  5  /


but I'm curious to how you would put quotes around each "reference" value.

Ex.

G REFERENCE
- --------------------
A 'SHOP 1', 'SHOP 2'
B 'SHOP 3', 'SHOP 4'
C 'SHOP 5'


I tried one technique and I get something like:

GRP	REFERENCE
A	'SHOP 1','SHOP 2'
B	'SHOP 3','SHOP 4'
C	'SHOP 5'






Re: GROUP_CONCAT equivalent on oracle - help needed [message #529141 is a reply to message #528781] Fri, 28 October 2011 15:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2010
Registered: January 2010
Senior Member
Michel,

AFAIK you need to install XMLDB.

SY.
Re: GROUP_CONCAT equivalent on oracle - help needed [message #529149 is a reply to message #529110] Fri, 28 October 2011 16:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2010
Registered: January 2010
Senior Member
lott42 wrote on Fri, 28 October 2011 14:45
I tried one technique and I get something like:


You need to "unescape" XML special characters. One possibility is DBMS_XMLGEN.CONVERT:

SQL> select  deptno,
  2          rtrim(
  3                xmlagg(
  4                       xmlelement(
  5                                  e,
  6                                  '''' || ename || '''',
  7                                  ','
  8                                 ).extract('//text()')
  9                      ),
 10                ','
 11               ) elist
 12    from  emp
 13    where deptno = 10
 14    group by deptno
 15  /

    DEPTNO ELIST
---------- -------------------------------------------------------
        10 'CLARK','KING','MILLER'

SQL> select  deptno,
  2          dbms_xmlgen.convert(
  3                              rtrim(
  4                                    xmlagg(
  5                                           xmlelement(
  6                                                      e,
  7                                                      '''' || ename || '''',
  8                                                      ','
  9                                                     ).extract('//text()')
 10                                          ),
 11                                    ','
 12                                   ),
 13                              1
 14                             ) elist
 15    from  emp
 16    where deptno = 10
 17    group by deptno
 18  /

    DEPTNO ELIST
---------- -------------------------------------------------------
        10 'CLARK','KING','MILLER'

SQL> 


SY.

[Updated on: Fri, 28 October 2011 16:17]

Report message to a moderator

Re: GROUP_CONCAT equivalent on oracle - help needed [message #529153 is a reply to message #529149] Fri, 28 October 2011 17:23 Go to previous messageGo to next message
lott42
Messages: 96
Registered: June 2010
Member
SY, Thanks, that seems to work fine. Cool
Re: GROUP_CONCAT equivalent on oracle - help needed [message #530528 is a reply to message #529153] Tue, 08 November 2011 17:13 Go to previous message
lott42
Messages: 96
Registered: June 2010
Member
SY, I modified the earlier query (slightly).

select     dbms_xmlgen.convert(
                                rtrim(
                                      xmlagg(
                                             xmlelement(
                                                        e,
                                                        '''' || ename || '''',
                                                        ','
                                                       ).extract('//text()')
                                            ORDER BY ename
                                            ),
                                      ','
                                     ),
                               1
                               ) elist
     from  emp
     where deptno = 10


Results:
'CLARK','KING','MILLER','MILLER'


BUT

if the "where" is changed from "10" to "100" we get:
Results:
"one row that has a NULL value"	


How do I get "no rows returned" like this query returns??

select  deptno,
            dbms_xmlgen.convert(
                                rtrim(
                                      xmlagg(
                                             xmlelement(
                                                        e,
                                                        '''' || ename || '''',
                                                        ','
                                                       ).extract('//text()')
                                           ),
                                     ','
                                    ),
                               1
                              ) elist
     from  emp
     where deptno = 100
     group by deptno



Previous Topic: Disadvantages of increasing INITRANS
Next Topic: replace temp table with collection
Goto Forum:
  


Current Time: Mon Sep 01 19:51:25 CDT 2014

Total time taken to generate the page: 0.06359 seconds