How to access elements in listagg in oracle?-CodePudding

For example, if I use listagg and get output as -

Customer_id	Listagg
A	(4, 6, 8, 9, 10)
B	(3, 4, 6, 8, 1)
C	(1, 2, 4, 6, 9)
D	(2, 9, 10)

If I want to print/select those rows which have 4 and 9, both in them (i.e., A and C in this case), how would I do it?

CodePudding user response：

Use a HAVING clause with conditional aggregation:

select
  customer_id,
  listagg (something, ', ') within group (order by something) as lst
from mytable
group by customer_id
having max(case when something = 4 then 1 else 0 end) = 1
   and max(case when something = 9 then 1 else 0 end) = 1
order by customer_id;

Another way to write the HAVING clause is with COUNT:

having count(case when something = 4 then 1 end) > 0
   and count(case when something = 9 then 1 end) > 0

CodePudding user response：

As JNevill commented, you'd rather do it before aggregating the string.

Not very pretty, but - see if it helps.

SQL> with temp (customer_id, c_listagg) as
  2    -- this is what your query returns
  3    (select 'A', '(4, 6, 8, 9, 10)' from dual union all
  4     select 'B', '(3, 4, 6, 8, 1)'  from dual union all
  5     select 'C', '(1, 2, 4, 6, 9)'  from dual union all
  6     select 'D', '(2, 9, 10)'       from dual
  7    )
  8  -- match 9 (or 4) at the beginning, in the middle and at the end of C_LISTAGG
  9  select customer_id
 10  from temp
 11  where (   c_listagg like '(9,%'
 12         or c_listagg like '%, 9,%'
 13         or c_listagg like '%, 9)')
 14     and (   c_listagg like '(4,%'
 15         or c_listagg like '%, 4,%'
 16         or c_listagg like '%, 4)');

CUSTOMER_ID
-----------
A
C

SQL>

CodePudding user response：

You can use an easy pattern matching with Oracle REGEXP_LIKE operator. It looks for the match (your number) if it is present inside the Listagg column:

preceeded by either space or open parenthesis [ (]
followed by either comma or closed parenthesis [,)]

SELECT * 
FROM tab
WHERE REGEXP_LIKE(Listagg, '[ (]4[,)]')
  AND REGEXP_LIKE(Listagg, '[ (]9[,)]')

Check the demo here.

CodePudding user response：

You should do it before aggregation; however, if you have to do it afterwards then:

SELECT customer_id,
       your_listagg_value
FROM   your_subquery
WHERE  ', ' || your_listagg_value || ', ' LIKE '%, 4, %'
AND    ', ' || your_listagg_value || ', ' LIKE '%, 9, %';

Which, for the sample data:

CREATE TABLE your_subquery (Customer_id, your_Listagg_value) AS
SELECT 'A', '4, 6, 8, 9, 10' FROM DUAL UNION ALL
SELECT 'B', '3, 4, 6, 8, 1' FROM DUAL UNION ALL
SELECT 'C', '1, 2, 4, 6, 9' FROM DUAL UNION ALL
SELECT 'D', '2, 9, 10' FROM DUAL;

Outputs:

CUSTOMER_ID YOUR_LISTAGG_VALUE

A 4, 6, 8, 9, 10

C 1, 2, 4, 6, 9

CUSTOMER_ID	YOUR_LISTAGG_VALUE
A	4, 6, 8, 9, 10
C	1, 2, 4, 6, 9

Note: if you have the leading and trailing brackets (and your values are numeric) then trim them from the string using TRANSLATE( your_listagg_value, ',()', ',' ).

db<>fiddle here