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

    Rows to columns

      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
          It is called pivoting. Please visit this link to find out how to do it ..


          If you believe that my answer is correct, then please mark the answer as correct.
          • 2. Re: Rows to columns
            Thank you Fateh for the link!
            • 3. Re: Rows to columns
              Aggregate function made result incorrect
              • 4. Re: Rows to columns
                "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
                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.