Printable View
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.
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.
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.