Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

List_agg function clarification

User_LCHKLJun 23 2015 — edited Jun 23 2015

We have two queries Query1 and Query2.I understood query2 output.if i remove group by deptno for query2 it is showing error  not a single group function.I believe group by is mandatory for LISTAGG function.In query 1 we are not using group by clause in a with clause.How query1 is running without group by clause and how it will do concatenation without group by clause?

Query1:

WITH t AS

  (SELECT 1 c1,'x' c2 FROM dual

 

  UNION

 

  SELECT 2 c1,'y' c2 FROM dual

 

  UNION

 

  SELECT 3 c1,'z' c2 FROM dual

  ) ,

  a AS

  (SELECT LENGTH(LISTAGG(c2, '') WITHIN GROUP (

  ORDER BY c2)) ln,

    LISTAGG(c2, '') WITHIN GROUP (

  ORDER BY c2) cont

  FROM t

  )

SELECT * from a

---

Query2:

select deptno,LISTAGG(sal, ',') WITHIN GROUP (

  ORDER BY sal) sal from emp

group by deptno

DEPTNO , SAL

101300,2450,5000
20800,1100,2975,3000,3000
30950,1250,1250,1500,1600,2850
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 21 2015
Added on Jun 23 2015
10 comments
3,554 views