Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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