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.
Is there any possibility of taking column in outer query to use it for order by but not using in the
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