Column Headers

[eflavin]

Hi there, my question is quite simple,is there another way of adding column headers to the spooled out data below in a way that each of the headers will go into an individual column in the excel file. The way of using the "From Dual" is a bit unsatisfactory in that all the headers are in one column.
thanks a million in advance

SET PAGESIZE 0
SET ECHO off
SET FEEDBACK ON
SET LINESIZE 500
SEt TRIMSPOOL ON


SPOOL C:\Product_Migrations\EPay_with_domain_name.csv
SELECT 'COMPANY_NAME PRODUCT_ID SALES_ID CUSTOMER_ID DOMAIN_NAME TOP_DOMAIN'
FROM DUAL;
select '"'||V_VALID_LN_SALES.COMPANY_NAME||'","'||
V_VALID_LN_SALES.PRODUCT_ID||'","'||
V_VALID_LN_SALES.SALES_ID||'","'||
V_VALID_LN_SALES.CUSTOMER_ID||'","'||V_VALID_LN_SALES.DOMAIN_NAME||'","'||V_VALID_LN_SALES.TOP_DOMAIN||'"'
from V_VALID_LN_SALES
where V_VALID_LN_SALES.PRODUCT_ID = 1884;
SPOOL OFF

 

[JHoarty]

There's probably a better way than this, but when I want output to ultimately end up in Excel, I like to comma-delimit it. Try this:



SELECT V_VALID_LN_SALES.COMPANY_NAME||',' as "Company Name,",
V_VALID_LN_SALES.PRODUCT_ID||',' as "Product ID,",
V_VALID_LN_SALES.SALES_ID||',' as "Sales ID,",
V_VALID_LN_SALES.CUSTOMER_ID||',' as "Customer ID,",
V_VALID_LN_SALES.DOMAIN_NAME||',' as "Domain Name,",
V_VALID_LN_SALES.TOP_DOMAIN as "Top Domain"
from V_VALID_LN_SALES
where V_VALID_LN_SALES.PRODUCT_ID = 1884;

Note the commas after the column aliases (within the double quotes), to be sure that each one occupies a separate column in Excel.

Also, you may want to format your columns using SQL+ formatting:

COL COMPANY_NAME FORMAT A12
COL PRODUCT_ID FORMAT 99999

THE "A12" refers to alphanumeric/12 characters, while the "99999" is a number mask. Helps you deal with the output when columns are otherwise too long.

Hope this helps.




John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com