4 Replies Latest reply: Jan 14, 2012 11:18 PM by 911102 RSS

    display dates in  a month as colums

    911102
      hi
      i have a requirement of displaying all the employees list with their presence and absence like the below one

      eg.


      empid 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan ...............31-Jan

      123 1 1 1 0 1 1
      321 0 1 0 1 1 1

      like this

      1-presence
      0-absence


      thanks..
      Arun
        • 1. Pivot
          Frank Kulash
          Hi, Arun,

          Welcome to the forum!
          908099 wrote:
          hi
          i have a requirement of displaying all the employees list with their presence and absence like the below one

          eg.


          empid 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan ...............31-Jan

          123 1 1 1 0 1 1
          321 0 1 0 1 1 1

          like this

          1-presence
          0-absence


          thanks..
          Arun
          That's called Pivoiting , and the best way to do it depends on your versions of Oracle. Always way which version (e.g. 10.2.0.4.0) you're using.
          See the forum FAQ "4. How do I convert rows to columns?"
          SQL and PL/SQL FAQ

          How is you data stored? Whenever you have a question, post CREATE TABLE and INSERT statements for your sample data.
          If your data looks like this:
          INSERT INTO table_x (empid, dt_present) VALUES (123, DATE '2012-01-01');
          INSERT INTO table_x (empid, dt_present) VALUES (123, DATE '2012-01-02');
          INSERT INTO table_x (empid, dt_present) VALUES (123, DATE '2012-01-03');
          INSERT INTO table_x (empid, dt_present) VALUES (123, DATE '2012-01-05'); 
          you'll need a different solution than if it is this way:
          INSERT INTO table_x (empid, dt, status) VALUES (123, DATE '2012-01-01', 'Present');
          INSERT INTO table_x (empid, dt, status) VALUES (123, DATE '2012-01-02', 'Present');
          INSERT INTO table_x (empid, dt, status) VALUES (123, DATE '2012-01-03', 'Present');
          INSERT INTO table_x (empid, dt, status) VALUES (123, DATE '2012-01-04', 'Absent');
          INSERT INTO table_x (empid, dt, status) VALUES (123, DATE '2012-01-05', 'Present'); ...
          and again different if it's like this
          INSERT INTO table_x (empid, start_dt, end_dt) VALUES (123, DATE '2012-01-01', DATE '2012-01-03');
          INSERT INTO table_x (empid, start_dt, end_dt) VALUES (123, DATE '2012-01-05', DATE '2012-01-06'); ...
          Will you be hard-coding the month (such as January, 2012) into the query?

          If your data is like the first example above, and if you are using Oracle 11.1 or higher, then you can do something like this:
          WITH   got_day_num         AS
          (
               SELECT     empid
               ,     EXTRACT (DAY FROM dt_present)     AS day_num
               FROM     table_x
               WHERE     dt_present     BETWEEN     DATE '2012-01-01'
                              AND     DATE '2012-01-31'
          )
          SELECT  *
          FROM     got_day_num
          PIVOT     (    COUNT (*)
               FOR  day_num     IN (1, 2, 3, 4, 5, ..., 31)
               )
          ;
          • 2. Re: Pivot
            911102
            hi thanks,
            i am using oracle 10.G version.there is no pivot.how can i do this,'

            my sample data...

            insert into emp_leave(empid,leavefrom,leaveto) values(123,'01-Jan-2012','03-Jan-2012')
            insert into emp_leave(empid,leavefrom,leaveto) values(122,'09-Jan-2012','13-Jan-2012')
            insert into emp_leave(empid,leavefrom,leaveto) values(111,'05-Jan-2012','13-Jan-2012')

            note :here month and year are user inputs.it may jan,feb ....not hardcoded

            now i want to display all empid's with their leaves in a grid with all dates and all employees for a month

            Edited by: 908099 on 14-Jan-2012 10:30
            • 3. Re: Pivot
              Frank Kulash
              Hi,
              908099 wrote:
              hi thanks,
              i am using oracle 10.G version.
              There is no version 10.F or 10.H, so it's not that helpful to say you have 10.G.
              Why not say "10.2.0.4.0", or whatever your actual version is?
              there is no pivot.how can i do this,'

              my sample data...

              insert into emp_leave(empid,leavefrom,leaveto) values(123,'01-Jan-2012','03-Jan-2012')
              Don't forget to post the CREATE TABLE statement. Without that, I can't tell what is the data type of leavefrom and leaveto.
              If they are DATEs, then don't try to INSERT VARCHAR2 values, such as '01-Jan-2012' into them. Use DATE literals (like I did) or TO_DATE.
              If they are VARCHAR2s, that's just asking for trouble. Always store date information in DATE columns.
              insert into emp_leave(empid,leavefrom,leaveto) values(122,'09-Jan-2012','13-Jan-2012')
              insert into emp_leave(empid,leavefrom,leaveto) values(111,'05-Jan-2012','13-Jan-2012')
              I don't see how that data could prioduce the results you requested:
              empid 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan ...............31-Jan

              123 1 1 1 0 1 1
              321 0 1 0 1 1 1
              Whenever you post a problem, make sure the resuilts you post are what you want from the sample data you post, and not some other data.
              note :here month and year are user inputs.it may jan,feb ....not hardcoded

              now i want to display all empid's with their leaves in a grid with all dates and all employees for a month

              Edited by: 908099 on 14-Jan-2012 10:30
              SELECT ... PIVOT only works in Oracle 11.1 (and higher).
              In earlier versions, you can pivot using CASE and GROUP BY, like this:
              VARIABLE  month_wanted     VARCHAR2 (8);
              EXEC      :month_wanted := 'Jan-2012';
              
              WITH      all_days      AS
              (
                   SELECT     LEVEL     AS day_num
                   ,     TO_DATE ( :month_wanted
                             , 'Mon-YYYY'
                             , 'NLS_DATE_LANGUAGE=ENGLISH'     -- If needed
                             ) + LEVEL - 1     AS dt
                   FROM     dual
                   CONNECT BY     LEVEL     <= 31
              )
              SELECT       el.empid
              ,       1 - COUNT (CASE WHEN day_num =  1 THEN a.dt END)     AS day_1
              ,       1 - COUNT (CASE WHEN day_num =  2 THEN a.dt END)     AS day_2
              ,       1 - COUNT (CASE WHEN day_num =  3 THEN a.dt END)     AS day_3
              ...
              ,       1 - COUNT (CASE WHEN day_num = 31 THEN a.dt END)     AS day_31
              FROM       emp_leave   el
              JOIN       all_days    ad  ON  ad.dt  BETWEEN  el.leavefrom
                                                     AND      el.leaveto
              GROUP BY  el.empid
              ;
              Without a CREATE TABLE statement, I can't test this.

              Depending on your requirements, you may want a LEFT OUTER JOIN instead of an inner JOIN, in the main query.

              For dynaic column headings (for example JAN_1 instead of DAY_1) you need dynamic SQL. That's not very hard using SQL*Plus substitution variables and COLUMN ... NEW_VALUE.
              • 4. Re: Pivot
                911102
                thanks a lot,
                Your query solved my probelm
                thanks