[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

Oracle query grouping questions

I have an oracle 11g table
tab1
----
f1      f2
A      p
A      q
B      r
C     m
A      s
B      g
C     x

How do I get this output with best performance? I need the query to run on a table that has 100K records.
O/P:
----
F1   F2
-----------
A     p,q,s
B     r,g
C     m,x
0
toooki
Asked:
toooki
  • 3
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Try this

SELECT
       f1,
       RTRIM(
           EXTRACT(XMLAGG(XMLELEMENT("s", tab1.f2 || ',')),
'/s/text()').getstringval(),
           ','
       )
           f2
FROM tab1
GROUP BY f1
/
0
 
slightwv (䄆 Netminder) Commented:
If 11gR2, there is a LISTAGG function that is probably better.
0
 
SharathData EngineerCommented:
0
 
slightwv (䄆 Netminder) Commented:
>>for all possible
I'm on mobile and can't fully read that link but it I could not find the XML method posted above.

With the exception of listagg, also mentioned above, the others are a little out-dated.

If you don't have 11gR2 and can use listagg, the XML method almost always out-performs the others listed in that link.
0
 
toookiAuthor Commented:
Many thanks.The LISTAGG function worked perfectly for me..
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now