4 Replies Latest reply: Mar 4, 2013 6:32 AM by 986838 RSS

    Rows to columns

    986838
      Hello everybody,

      I want to display rows as columns, my select is:

      select id, to_char(col2,'dd-mm-yyyy') , to_char(col2,'hh:mi PM') , action from atable

      sample output is:
      id day* time action*
      1 *02-02-2013* 10:10 AM IN
      1 *02-02-2013* 08:40 PM OUT

      I wonder if I could display the output as:

      id date* IN OUT*
      1 *02-02-2013* 10:10 AM *08:40 PM*


      I'm using Oracle 11g R2
      Windows 7
      Oracle Apex4.2
      Glassfish server open source

      Thanks for the time you spent reading my Question..
        • 1. Re: Rows to columns
          Mindmap
          Hello,
          It is called pivoting. Please visit this link to find out how to do it ..

          http://www.oracle-developer.net/display.php?id=506

          Regards,
          Fateh
          -----
          If you believe that my answer is correct, then please mark the answer as correct.
          • 2. Re: Rows to columns
            986838
            Thank you Fateh for the link!
            • 3. Re: Rows to columns
              986838
              Aggregate function made result incorrect
              • 4. Re: Rows to columns
                986838
                "Pivot" worked great .. but since the same ID may have multiple IN and OUT entries on same date .. using aggregate function will choose only a single value for IN and OUT.

                My code looks like
                 
                WITH st_pivot AS 
                ( 
                SELECT ID , TO_CHAR(TIME,'dd-mm-yyyy') AS EDAY  ,  action , time 
                FROM timesheet_exercise
                )  
                
                SELECT * FROM st_pivot
                PIVOT
                (  
                MAX(TO_CHAR(TIME,'hh:mi PM')) FOR ACTION IN (1, 2 , 3)
                )
                ----------

                If want to display all IN and OUT entries for the same id for a specific date, how to do it ?


                Any help please ?

                Thank you so much for your time.