Display a rows horizontaly dynamically
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
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.
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;
OPEN c_dummy FOR p_slct;
FETCH c_dummy INTO lc_colval;
EXIT WHEN c_dummy%NOTFOUND;
lc_str := lc_str || p_dlmtr || lc_colval;
Click Here to Expand Forum to Full Width