How to do pivot on date column ?

User_XWOGD
User_XWOGD Member Posts: 1 Green Ribbon

--COMMA SEPARATED DATE

 SELECT LISTAGG('''' || "rdate" || ''' as "' || "rdate" || '"', ',' ON OVERFLOW TRUNCATE WITH COUNT) WITHIN GROUP(ORDER BY "rdate")

    INTO v_cols

    FROM ( SELECT DISTINCT "rdate"

        FROM READING 

        WHERE "nodeID" = 21

             AND "isDeleted" != 1 ) T

    ORDER BY "rdate" DESC; 

 DBMS_OUTPUT.PUT_LINE('Dates = ' || TO_CHAR(v_cols)); 





 WITH CTE AS(SELECT RP."promptID", RP."prompt",

           NVL(RC."readstyle", 1) AS "readstyle",

           NVL(RC."magdir", 0) AS "magdir",

           NVL(RP."readconfigID", 0) AS "readconfigID" ,

           RA."alarmID",RA."retired",RP."pvcOrAnode",RP."radius",RP."feet",

           RA."userorder",RD."rdate",RD."nodeID",RD."rdata",RD."odata",

           RD."remark" 

     FROM READPROMPT RP

     LEFT JOIN READCONFIG RC ON RP."readconfigID" = RC."readconfigID"

     INNER JOIN READALARM RA  ON RP."promptID" = RA."promptID"

     LEFT JOIN READING RD  ON RA."alarmID" = RD."alarmID"

     AND RD."isDeleted" != 1 )

     SELECT * FROM

     (

      SELECT "alarmID","promptID","prompt","readstyle","magdir","readconfigID","retired","userorder","rdate",

          CONCAT( CONCAT( "rdata", ',' ), "odata" ) AS "rodata"

      FROM CTE ) T 

       PIVOT

      (

         MAX("rodata")

--WORKING

        -- For "rdate" in ('03-03-21' as "03-03-21",'11-03-21' as "11-03-21",'12-03-21' as "12-03-21")

-- NOT WORKING

         For "rdate" in (' || v_cols || ')

      );


Getting this error on execute this query. But when i put manual date string same as blue character string you can see in screenshot then it works.


How to use dynamic comma separated string when we want to pivot on date column in Oracle ?


Any help would be appreciated