0
SELECT substr(cfe.file_name,10),
       cfe.source_file_date,
       count(cs.cash_settlement_key)
FROM   CASH_SETTLEMENT CS
       INNER JOIN CASH_FILE_EVENT CFE
       ON CFE.CASH_FILE_EVENT_KEY=CS.CASH_FILE_EVENT_KEY
WHERE  CFE.SOURCE_FILE_DATE>sysdate-30
and    to_char(CFE.SOURCE_FILE_DATE,'DAY')=to_char(sysdate,'DAY')
and    file_name like '%OIDFC_FUNDING_REPORT.txt%'
group by
       substr(cfe.file_name,10),
       cfe.source_file_date

enter image description here

I want output like below enter image description here

I tried using pivot, but don't know where I am going wrong.

SELECT *
FROM   (
  SELECT substr(cfe.file_name,10),
         cfe.source_file_date,
         count(cs.cash_settlement_key)
  FROM   CASH_SETTLEMENT CS
         INNER JOIN CASH_FILE_EVENT CFE
         ON CFE.CASH_FILE_EVENT_KEY=CS.CASH_FILE_EVENT_KEY
  WHERE  CFE.SOURCE_FILE_DATE>sysdate-30
  and    to_char(CFE.SOURCE_FILE_DATE,'DAY')=to_char(sysdate,'DAY')
  and    file_name like '%OIDFC_FUNDING_REPORT.txt%'
  group by
         substr(cfe.file_name,10),
         cfe.source_file_date
)
PIVOT (
  COUNT(CFE.cash_settlement_key) FOR cfe.source_file_date IN (
    CFE.SOURCE_FILE_DATE>sysdate-30
    and to_char(CFE.SOURCE_FILE_DATE,'DAY')=to_char(sysdate,'DAY')
  )
)
MT0
  • 143,790
  • 11
  • 59
  • 117
Shreya Deep
  • 3
  • 1
  • you have to use oracle's pivot function. Already answered [on here](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) – James Feb 18 '20 at 23:13
  • When using pivot it wants me to specify distinct values in for clause, but in my case the in values are changing each day, complex for is not allowed. – Shreya Deep Feb 18 '20 at 23:50
  • You can't do a dynamic pivot in Oracle. Either use PL/SQL to dynamically generate a SQL query with the correct static values or you can use Pivot XML to get the output as XML or, the better solution, is to not try doing a dynamic pivot in Oracle but to do it in whatever middle-tier or front-end you're using to access the database. – MT0 Feb 18 '20 at 23:53
  • The sql you referred to has in values to specify, but in my case the date and count values is getting generated by diff function which would change each day. So please dont close the question – Shreya Deep Feb 19 '20 at 00:11

0 Answers0