Skip to Main Content
  • Questions

  • Concatenate the rows WITH NO column to group

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, V Raja Sekhar.

Asked: September 07, 2017 - 9:14 am UTC

Last updated: September 07, 2017 - 10:00 am UTC

Version: 10.2.0.1

Viewed 10K+ times! This question is

You Asked

Hello Experts,

I am preparing some stored procedure and there is a need to group the result into a string with any delimiter. The challenge here is I dont have any column ( with same data ) to group. Below is my scenario. 


CONTACT_NO     AREA
==========     =====
+4484948498    'BOURNEMOUTH BH1AA'
+4494947499    'BOURNEMOUTH CKKAA'
+4404848598    'BOURNEMOUTH MBKKA'


RESULT
======
+4484948498,+4494947499,+4404848598


I have one solution already by using subquery and like to know if there are any alternative ways of achieving this.

SELECT CONTACT_NO FROM ( select CONTACT_NO, 'COL3' TEMP from contacts ) GROUP BY TEMP;


Thank you,
Raj.


and Chris said...

I don't understand what's the significance of having "no column to group"?

From 11.2 you can use listagg:

with rws as (
  select chr(rownum+64) l from dual
  connect by level <= 3
)
  select listagg(l, ',') within group (order by l) 
  from   rws;

LISTAGG(L,',')WITHINGROUP(ORDERBYL)  
A,B,C


But as you're stuck on 10g, you'll have to go for a workaround such as using Tom's stragg() function:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:2196162600402

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

V Raja Sekhar Pothuri, September 08, 2017 - 2:52 pm UTC

I don't understand what's the significance of having "no column to group"?
Ans: I want to avoid loop in stored procedure for a result set returned by a 'select' query.

with loop
=========
for cur_subsc in (select contact_no from address_master where area_code .... )
loop
////
loop end

with out loop
=============
select LISTAGG( tab1.col1, ',') WITHIN GROUP ( ORDER BY tab1.col1) TEMP into contactNos from table1 tab1;
select LISTAGG( tab2.col2, ',') WITHIN GROUP ( ORDER BY tab2.col2) TEMP1 into plans from table2 tab2 where tab2.col1 in ( contactNos );


Thanks for sharing the solution, I am able to implement this.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.