I have a scenario. I need to use listagg in 2 columns to put the data comma(,) separated. I have aliases in query so I can not use group by which is mandatory in listagg. To resolve that I am taking the output in outer query and using listagg there and grouping the column.

Problem is my data is sorted by a column which is not in select clause. how can I maintain the order of data of inner query in outer query. Or

Is there any possibility of taking column in outer query to use it for order by but not using in the select clause.

My query is like

    select A+10 AA,
    B*20 BB,
    CC ,
    DD
    from Tab1 order by M;

when I am using listagg then i modified the query

    select
    AA,
    BB
    listagg(CC,',') within group(order by CC),
    listagg(DD,',') within group(order by DD)
    from
    (
    select A+10 AA,
    B*20 BB,
    CC ,
    DD
    from Tab1 order by M
    )
     group by AA,BB

If you can see clearly our inner query is sored by column M which is not in select clause. How can i maintain order by M without taking in outer query or if i take column M in outer query how can i exclude the result.

Or is there any possibility of using listagg in inner query when I have aliases

1 Answers

1
Chrᴉz On

You can use aliases while grouping and you can use columns you don't display to sort. The only restriction is that when not being grouped by, the columns to sort by need to be aggregated (sum, max, min, etc).

with testtab as (
  select 1 "AA", 10 "BB", 'aval1' "CC", 'bval1' "DD", 5 "SORTV" from dual
  union all select 2, 10, 'aval2', 'bval2', 4 "SORTV" from dual
  union all select 2, 30, 'aval3', 'bval3', 3 "SORTV" from dual
  union all select 4, 40, 'aval4', 'bval4', 2 "SORTV" from dual
  union all select 4, 40, 'aval5', 'bval5', 1 "SORTV" from dual)
select aa + 10 as "AA",
  bb * 20 as "BB",
  listagg(cc, ', ') within group (order by cc),
  listagg(dd, ', ') within group (order by dd)
from testtab
group by aa, bb
order by max(SORTV) asc

yields

AA     BB   LISTAGG(CC,',')WITHINGROUP(ORDERBYCC)     LISTAGG(DD,',')WITHINGROUP(ORDERBYDD)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------
14     800  aval4, aval5                              bval4, bval5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
12     600  aval3                                     bval3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
12     200  aval2                                     bval2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
11     200  aval1                                     bval1