Categories
How to do pivot on date column ?
--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