CODEWITHSUNDEEP
sqloracle-databaselistagg
SHRLY
SHRLY

Reputation: 241

ORACLE SQL LISTAGG with case when I get more then 100 results

Need get from DB some values as> "value1, value2, ... value100" but in case when I will recive more then 100 values need to do "value1, value2, ... value100.." to know it is not all values but I want show maximal 100 values now I using 

select LISTAGG(CASE WHEN ROWNUM <=100 THEN within.number ELSE NULL END,', ') 
         WITHIN GROUP (ORDER BY within.number )
from ...........

but that does not works as I need.

Upvotes: 0

Views: 2306

Answers (1)

Boneist
Boneist

Reputation: 23578

You can do this like so:

WITH sample_data AS (SELECT 1 ID, 'a' val FROM dual UNION ALL
                     SELECT 1 ID, 'b' val FROM dual UNION ALL
                     SELECT 1 ID, 'c' val FROM dual UNION ALL
                     SELECT 2 ID, 'd' val FROM dual UNION ALL
                     SELECT 2 ID, 'e' val FROM dual UNION ALL
                     SELECT 2 ID, 'f' val FROM dual UNION ALL
                     SELECT 2 ID, 'g' val FROM dual UNION ALL
                     SELECT 3 ID, 'h' val FROM dual UNION ALL
                     SELECT 3 ID, 'i' val FROM dual UNION ALL
                     SELECT 3 ID, 'h' val FROM dual UNION ALL
                     SELECT 3 ID, 'j' val FROM dual UNION ALL
                     SELECT 3 ID, 'k' val FROM dual UNION ALL
                     SELECT 4 ID, 'l' val FROM dual UNION ALL
                     SELECT 4 ID, 'm' val FROM dual UNION ALL
                     SELECT 5 ID, 'n' val FROM dual)
SELECT ID,
       listagg(CASE WHEN rn <= 3 THEN val ELSE '...' END, ',') WITHIN GROUP (ORDER BY val) vals
FROM   (SELECT ID,
               val,
               row_number() OVER (PARTITION BY ID ORDER BY val) rn
        FROM   sample_data)
WHERE  rn <= 4 -- max amount of expected elements + 1
GROUP BY ID;

 ID VALS
--- -----------
  1 a,b,c
  2 d,e,f,...
  3 h,h,i,...
  4 l,m
  5 n

In my example, I want to display just three elements, along with "..." if there are other elements available. So, first off, we filter the results to just the first four rows for each id.

To do that, I used the ROW_NUMBER analytic function to label each row with a number in ascending val order for each id.

Once we know the row numbers, we can filter the rows to return the expected number of elements + 1 - we need the extra row to know if there are more rows available or not. In my case, that means we need to get the first 4 rows.

Next, we need a case statement to output the actual value for the first three elements, and "..." for the fourth element, if present.

Then we can incorporate that into the LISTAGG and voila!

Of course, the above assumes that your database isn't at version 12.2 - if it is, then you can take advantage of the new overflow enhancements - see here for more information

Upvotes: 2

Related Questions