HELP!!! Column Headers and Column Values [message #21706] |
Fri, 23 August 2002 11:14 |
Melissa
Messages: 65 Registered: January 2000
|
Member |
|
|
I have been trying unsucessfully to build an oracle select statement that translates a field column (f.TextLabel) and its data values.
Ultimately, what I would like the outcome to be is to select this field column (f.TextLabel) and have its field values appear as the column headers in an excel spreadsheet.
What is unique about the problem is that f.TextLabel is a variable list for each a.AssetTag (meaning each assettag may have 1 or more text label).
Below is my sql statement. What do I need to do to solve this puzzle?
select a.AssetTag, f.TextLabel, x.ValString from amAsset a, amProduct p, amCategory c,amEmplDept e, amStock s, amLocation l, amFVAsset x, amFeature f where a.lProdID = p.lProdID and a.lCategId = c.lCategId and a.lUserId = e.lEmplDeptId and (a.lAstID = x.lAstID and x.lFeatID = f.lFeatID) and (a.lstockId = s.lstockId and s.llocaid = l.llocaid)
Thank you in advance for you help. mburbage@kpmg.com
|
|
|
Re: HELP!!! Column Headers and Column Values [message #21710 is a reply to message #21706] |
Fri, 23 August 2002 18:33 |
Eric Myers
Messages: 13 Registered: August 2002
|
Junior Member |
|
|
I assume your query returns the data you want in SQL*Plus but the data outputs vertically on the screen as usual. You want this data to go horizontal in Excel in a row so it can be column headers.
Have you tried running the query then highlighting the output then go to excel and highlight an equivalent amount of cells in a row and do a paste?
Eric
|
|
|
|
|
Re: HELP!!! Column Headers and Column Values [message #21978 is a reply to message #21706] |
Fri, 13 September 2002 10:32 |
Dhiren
Messages: 43 Registered: July 2002
|
Member |
|
|
You can check out the help of utl_file utility.
How it is used is you have to write a plsql block(
procedure or function).
I will give an off hand example for you.
declare
cursor c1 is select * from emp;
h_fhandle utl_file.file_type;
h_file_path varchar2(30) default 'c:abc';--(must be in the utl_file_dir of init.ora)
h_file_name varchar2(30) default 'out.dat';
begin
h_fhandle = utl_file.fopen(h_file_path,h_file_name, w);
for c1rec in c1
loop
h_record_buffer := c1rec.ename;
utl_file.put_line(h_fhandle, h_record_buffer );
end loop;
utl_file.fclose(fhandle);
end;
You can also write various exceptions for utl_file
Hope this helps.
|
|
|
|