Display a rows horizontaly dynamically
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Display a rows horizontaly dynamically

  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Display a rows horizontaly dynamically

    Hello,

    I am a newbie in oracle, just wondering how to extract this tables.
    here is the records in a table look like

    acc_id year amt

    001 2004 25.5
    001 2005 30.0
    001 2006 1.5

    002 2002 1.25
    002 2003 6.0
    002 2004 3.5

    003 2010 10.00

    they want me to extract in csv look like this.

    acc_id 2002 2003 2004 2005 2006 2010
    001 25.5 30.0 1.5
    002 1.25 6.0 3.5
    003 10.00

    Thank you.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    This is homework, isn't it?

    One of the solutions is a pivot table, feel free to research the topic either in this very same forum or the net.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Oct 2010
    Posts
    3

    Horizontal Row

    Hi,

    I managed to display it horizontally using a rowtocol() function I got in the internet. However my problem is how to put the year in a proper column,
    Assuming I have a table compose of a min(year) = 2002 and max(year) 2010.
    Here is a tables records.

    This is just one of the records, the year varies , sometimes one acount id only have one year and some even have from 2002 to 2010.

    1234 2002 12.5
    1234 2004 6.00

    1233 2006 2.05
    1233 2007 11.00

    1444 2009 12.02
    1444 2010 6.55

    my instructor wants it to extract this way

    heading in the excell output file should look like this.

    acc_id 2002 2003 2004 2005 2006 2007 2009 2010
    1234 12.5, null, 6.00, null, null, null, null, null
    1233 null, null, null, null, 2.05,11.00, null, null
    1444 null, null, null, null, null, null, 12.02, 6.55

    This is the function i took from the internet. It works fine except I don't get the amount in proper column

    CREATE OR REPLACE FUNCTION
    rowtocol( p_slct IN VARCHAR2, p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2

    AUTHID CURRENT_USER AS

    TYPE c_refcur IS REF CURSOR;

    lc_str VARCHAR2(4000);
    lc_colval VARCHAR2(4000);
    c_dummy c_refcur;
    l number;

    BEGIN

    OPEN c_dummy FOR p_slct;

    LOOP
    FETCH c_dummy INTO lc_colval;

    EXIT WHEN c_dummy%NOTFOUND;

    lc_str := lc_str || p_dlmtr || lc_colval;

    END LOOP;

    CLOSE c_dummy;

    RETURN SUBSTR(lc_str,2);

    Thanks.

Posting Permissions

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


Click Here to Expand Forum to Full Width