Home » SQL & PL/SQL » SQL & PL/SQL » Horizontal Output from ORACLE SQL
icon5.gif  Horizontal Output from ORACLE SQL [message #220432] Tue, 20 February 2007 12:37 Go to next message
jilpangs
Messages: 16
Registered: April 2005
Junior Member
Hi,

I have around 200 columns in a table.
I'm using all_tab_columns to fetch the column names. which displays
Col1
Col2
Col3
etc...

I need the output as
Col1,Col2,Col3...how to do this ?

Thanks
Re: Horizontal Output from ORACLE SQL [message #220447 is a reply to message #220432] Tue, 20 February 2007 13:15 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
What do you want to do with the single line output. Is it for external or oracle processing?
icon5.gif  Re: Horizontal Output from ORACLE SQL [message #220450 is a reply to message #220447] Tue, 20 February 2007 13:43 Go to previous messageGo to next message
jilpangs
Messages: 16
Registered: April 2005
Junior Member
external , meaning..i need to find sum of those columns.

Select sum(cola),sum(colb) etc... from table a

Re: Horizontal Output from ORACLE SQL [message #220456 is a reply to message #220450] Tue, 20 February 2007 14:16 Go to previous message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Got you now... I thought you wanted a single value as the output.

If you want to calculate the sums of specific columns in a table (e.g. table name = BB_BASKET) for all numeric fields, you could run the output of this query:

SELECT 'SELECT' FROM dual
UNION ALL
SELECT 'SUM(' || column_name || '),'
FROM user_tab_columns utc
WHERE utc.TABLE_NAME = 'BB_BASKET'
AND utc.DATA_TYPE = 'NUMBER'
UNION ALL
SELECT DISTINCT 'FROM ' || table_name || ';'
FROM user_tab_columns utc
WHERE utc.TABLE_NAME = 'BB_BASKET';

you get (manually remove the comma on the last column):
SELECT
SUM(IDBASKET),
SUM(QUANTITY),
SUM(IDSHOPPER),
SUM(ORDERPLACED),
SUM(SUBTOTAL),
SUM(TOTAL),
SUM(SHIPPING),
SUM(TAX),
SUM(PROMO)
FROM BB_BASKET;

Previous Topic: Correlated Subquery ?
Next Topic: Reach the highest PLSQL performances....
Goto Forum:
  


Current Time: Thu Dec 08 21:51:44 CST 2016

Total time taken to generate the page: 0.13711 seconds