Oracle SQL Listagg Function

How to use Oracle's LISTAGG function with a unique filter?

I don't have an 11g instance available today but could you not use:

SELECT group_id,
       LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS names
  FROM (
       SELECT UNIQUE
              group_id,
              name
         FROM demotable
       )
 GROUP BY group_id

Using LISTAGG function in Oracle

You can do it after the join:

SELECT s.sku,t.product,
       LISTAGG(t.id, ',') WITHIN GROUP (ORDER BY id) AS catids
FROM category t
INNER JOIN prod s
 ON(t.product = s.prod)
GROUP BY t.product,s.sku;

listagg in oracle sql

Oracle does not support distinct for listagg() prior to Oracle 19.

In your case, though, the problem is presumably caused by a Cartesian product as a result of the joins. That suggests that correlated subqueries are a better approach:

select t.*,
       (select listagg(a.name, ',') within group (order by a.id)
        from A a
        where a.id = t.id 
       ) as a,
       (select listagg(b.name, ',') within group (order by b.id)
        from B b
        where b.id = t.id 
       ) as b
from some_table t
where t.status in (1, 2, 3)
group by t.id;

Translating oracle sql listagg function to Bigquery

Below should work

,string_agg(NODE_NAME 
                        || ':'
                        || CASE
                                WHEN RESPONSE IS NULL THEN
                                    PROMPT_STATUS
                                ELSE
                                    RESPONSE
                            END, '; ' 
                        )  OVER(
                            PARTITION BY CALL_KEY 
                            ORDER BY DIALOG_SEQ  
                            ROWS BETWEEN UNBOUNDED PRECEDING
                            AND UNBOUNDED FOLLOWING
                        ) FLOW_SEQ

ORACLE SQL listagg function

Are you using 11.1 or 11.2? LISTAGG was introduced in 11.2, it was not available in 11.1.

Your SQL statement looks valid to me in 11.2. But you'd get an error in 11.1 and ORA-00923 would seem like a reasonable error in 11.1.

---

Related Topics