Pivot distinct column values into columns using Oracle SQL developer causes error ORA-01489

Question

I am trying to pivot distinct column values into column in Oracle SQL Developer using dynamic SQL, there are more than 400 and each word contains average of 50 characters distinct columns values, I have used clob as a datatype to store the concatenated values but I end up getting this error:

ORA-01489 result of string concatenation is too long

I tried listagg and xmlagg and stored result into clob variable but issue still persists.

Here is the sample code:

declare
    v_clob_data clob;
    dynamic_sql clob;

begin
    v_clob_data := empty_clob();

    select 
        listagg(''''||category_name||''''||' as "'||category_name||'"',',') within group (order by category_name)
into v_clob_data
from (select distinct categories as category_name from my_table);

    dynamic_sql :='
        select * from 
        (
            select 
                id,
                categories
            from my_table        
        )
        pivot(
            count(categories) for categories in ('||v_clob_data||')
        )';

    execute immediate dynamic_sql;
end;

my_table looks like this:

id	categories
1	A
1	A
1	B
1	B
1	B
1	C
1	C
2	B
2	B
3	A

This is the expected result:

id	A	B	C
1	2	3	2
2	0	2	0
3	1	0	0

Keep in mind the categories column has average 50 characters for each value and more than 400 values (it can be increase or decrease). Hence dynamic SQL is necessary. Please help

Answer 1

Got the answer from this post

I was having trouble with xmlagg syntax. resolved it by referring the above link.