Convert ` LISTAGG` TO `XMLAGG`

Question

How to convert LISTAGG with case statements to XMLAGG equivalent, so as to avoid the concatenation error.

@ECHO ${cols_2 ||32767||varchar2}$ --Declare variable

SELECT LISTAGG( 'MAX(CASE WHEN CATEGORY = '''||CATEGORY||''' THEN "'||"LEVEL"||'" END) AS "'||"LEVEL"||'_'||CATEGORY||'"' , ',' )
          WITHIN GROUP( ORDER BY CATEGORY, "LEVEL" DESC )
    INTO cols_2
    FROM (
          SELECT DISTINCT "LEVEL", CATEGORY
            FROM temp
          );

I tried this and I'm getting an error saying missing keyword

@ECHO ${cols_2 ||32767||varchar2}$ --Declare variable

select rtrim (
          xmlagg (xmlelement (e, 'MAX(CASE WHEN CATEGORY = '''||CATEGORY||''' THEN "'||"LEVEL"||'" END) AS "'||LEVEL||'_'||CATEGORY||'"', ',') order by 1,2 desc).extract (
             '//text()'),
          ', ')
          INTO cols_2
    FROM (
          SELECT DISTINCT "LEVEL", CATEGORY
            temp
          );

I have tried this an declared the cols_2 as clob type :-

SELECT DBMS_XMLGEN.CONVERT (
           RTRIM (
               XMLAGG (XMLELEMENT (
                           e,
                              'MAX(CASE WHEN CATEGORY = '''
                           || CATEGORY
                           || ''' THEN "'
                           || "LEVEL"
                           || '" END) AS "'
                           || "LEVEL"
                           || '_'
                           || CATEGORY
                           || '"',
                           ',')
                       ORDER BY 1, DESC).EXTRACT('//text()').getclobval(),','),1)
               ', '),
           1)
  INTO cols_2
  FROM (SELECT DISTINCT "LEVEL", CATEGORY
          FROM temp);

Yet my issue is not resolved ,Im getting an error while trying to execute it as a procedure like :- Error in concatenation of `LISTAGG` function[Not a duplicate question]

Answer 1

You are getting the missing keyword error because you are most likely attempting to run the second query as a standalone query instead of in a PL/SQL block. When you are doing that, you have to remove your into cols_2 clause. That is your immediate issue that should resolve your error.

Also, based on your prior question, using the XML functions will escape your ' and " characters so you will want to make sure to unescape them back to their original characters so you can use them in your dynamic sql query like this:

SELECT DBMS_XMLGEN.CONVERT (
           RTRIM (
               XMLAGG (XMLELEMENT (
                           e,
                              'MAX(CASE WHEN CATEGORY = '''
                           || CATEGORY
                           || ''' THEN "'
                           || "LEVEL"
                           || '" END) AS "'
                           || "LEVEL"
                           || '_'
                           || CATEGORY
                           || '"',
                           ',')
                       ORDER BY 1, 2 DESC).EXTRACT ('//text()'),
               ', '),
           1)
  --INTO cols_2
  FROM (SELECT DISTINCT "LEVEL", CATEGORY
          FROM temp);