0

I am trying to convert columns into rows. I have input data like below in the Oracle Table EMP -

enter image description here

At runtime, user can pass their own start time and end time, for ex suppose - 15-Dec-2020 to 31-Dec-2021. In this case, he should get the result like below for each MONTH END which falls between these dates -

enter image description here

Here "1" is nothing but the value of HEADCOUNT column value.

so MONTH END columns names are generating dynamically.

I tried below query but it is not fullfilling my requirement. DATE_PERIOD is a normal date time table having all the dates details(like month end, day name, quarter etc) for next 20 years). I tried PIVOT function as well in oracle but not able to generate the desired output. Can someone please help!!

SELECT L.EMP_ID, LISTAGG(D.MONTH_END_DATE,',') WITHIN GROUP(ORDER BY D.MONTH_END_DATE) AS PERIODS FROM EMP L INNER JOIN (SELECT DISTINCT MONTH_END_DATE FROM DATE_PERIOD WHERE MONTH_END_DATE BETWEEN '15-DEC-2020' TO '31-DEC-2021') D ON D.MONTH_END_DATE >= L.START_DATE AND D.MONTH_END_DATE <= L.END_DATE AND L.EMP_ID = 'E001';

enter image description here

I am able to write another query to produce the MONTH_END_DATE in the rows. enter image description here

enter image description here

Mohit
  • 25
  • 4
  • 2
    ideally this pivot is done in the front-end UI where the user is also making their range selection. i.e. just because it can be done in SQL does not mean it should be done there. Also: I suggest you read these: [Provide a `Minimal Complete Verifiable Example` (MCVE)](https://stackoverflow.com/help/mcve) and [Why should I provide a MCVE](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Paul Maxwell Apr 19 '21 at 04:17
  • @PaulMaxwell Thanks. Yes. it is the requirement of front-end.will ask UI team now to work on it. I just trying to check if it is easy to do it at the db level. – Mohit Apr 20 '21 at 12:26
  • @astentx thanks. good link. Will check it. – Mohit Apr 20 '21 at 12:27

0 Answers0