This discussion is archived
4 Replies Latest reply: Mar 4, 2013 4:32 AM by 986838 RSS

Rows to columns

986838 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you Fateh for the link!
  • 3. Re: Rows to columns
    986838 Newbie
    Currently Being Moderated
    Aggregate function made result incorrect
  • 4. Re: Rows to columns
    986838 Newbie
    Currently Being Moderated
    "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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points