Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    1

    Unanswered: Dynamic row to column in Oracle 8i (like pivot)

    Hi Experts,

    I know to use the pivot by knowing the row value and converting into column name but since I don't know what is row value I want to display all the rows into column and below each column the amount to be displayed.

    My SQL is like this

    select a.CONT_NO,a.CONT_DATE,b.DESCRIPTION,b.COMM_AMT
    from css_cont_hdr a, css_cont_special_comm b
    where a.CFM_DATE between '01-JAN-07' AND '01-FEB-07'
    and a.REF_NO=b.CTHD_REF_NO
    ORDER BY 1;

    My current output like this

    CONT_NO CONT_DATE DESCRIPTION COMM_AMT
    TCTU602388 26-Dec-06 ACTUAL COMM 500
    TCTU602388 26-Dec-06 SPOTTER'S COMM 1250
    TCTU602388 26-Dec-06 OVERTRADE COMM 3000
    TCTU602389 26-Dec-06 ACTUAL COMM 500
    TCTU602389 26-Dec-06 OVERTRADE COMM 3000
    TCTU602389 26-Dec-06 SPOTTER'S COMM 1250
    TCTU700096 12-Jan-07 ACTUAL COMM 500
    TCTU700096 12-Jan-07 TAILGATE COMM 738
    TCTU700096 12-Jan-07 SPOTTER'S COMM 900
    TCTU700102 13-Jan-07 ACTUAL COMM 750
    TCTU700102 13-Jan-07 DEALER COMM - EK AUTO (EXPORT) PTE LTD 900
    TCTU700102 13-Jan-07 BOX COMM 500
    TCTU700153 16-Jan-07 ACTUAL COMM 750
    TCTU700153 16-Jan-07 DEALER COMM (KRAFTWAGEN) 1450
    TCTU700153 16-Jan-07 CANOPY COMM 150

    I want to description values to be converted into column and the comm_amt below the description the description will not be same it may vary you can see the above example.

    Like

    Cont_no cont_date ACTUAL COMM SPOTTER'S COMM OVERTRADE COMM
    TCTU602388 26-Dec-06 500 1250 3000


    Can any expert help me in this regard?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking



    Ask Tom has the answer.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •