output data for each month and dynamically creating column as per user input

[ritesh.verma]

I have a very specific question. I hope someone can help me.

 

My query needs to prompt for "TYPE"(see below column) and "start date" and "end date" from user, based on user input the query would run on source data to generate output as outlined below. My below example assumes the input value are

 

TYPE: ID

Start Date: 31DEC2016

End Date: 30 Jun 2017

 

   Source tables:

   EMPDETAILSextended

    Staff number     Date             TYPE

     1            25JAN2017          ID

     1            30JAN2017          ID

     1            04JUN2017          ID

     2            01JAN2017          ID

     2            01FEB2017          ID

     2            28Mar2017          ID

 

  EMPDETAILS

 

  Staff number    InitialDate             

     1            01JAN2017      

     2            01FEB2017          

 

   Output (see the columns for month are generated dynamically) 

  StaffNumber    InitialDate      JAN17    FEB17    MAR17  APR17  MAY17  JUN17 

     1             01JAN2017      30JAN17                               04JUN17

     2             01FEB2017      01JAN17  01FEB17  28mar17

I am comfortable in getting this data but I am not sure how to output all this to dynamically create column name with the header as that specific month. I am using Oracle12C.

 

I dont expect you to write the whole query for me but I am unable to figure out the approach and hence failing to formulate a working query?

[mlopez_1]

With a crosstab.

Mariano