Home » SQL & PL/SQL » SQL & PL/SQL » Select column Dynamically
Select column Dynamically [message #212543] Fri, 05 January 2007 15:33 Go to next message
Messages: 11
Registered: September 2006
Location: Mumbai
Junior Member

Hi All,
Wish you all a very happy new year !!!
I have a requirement as follows:
I want to select all the column(s) from one table where SUM of that column is not 0 dynamically i.e.

Let say data in a table is:

-- ---------- ---------- ---------- ---------- ----------
05 0 0 0 12455.412 0
06 0 0 104184.204 1562378.4 0
07 0 0 0 0 0

I want the output like this -- Dynamically:
-- ---------- ----------
05 0 12455.412
06 104184.204 1562378.4
07 0 0

Please help me out in geting this query.
Thanks in advance.

Re: Select column Dynamically [message #212561 is a reply to message #212543] Fri, 05 January 2007 17:30 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can't do this in straight SQL. Quite simply, a SQL must have a fixed number of columns in the SELECT clause, and they must all be projected.

You can use your SQL client to help. For example, with SQL*Plus, a line such as:
will suppress printing of the CBB column.

However you need to submit these statements BEFORE you run the SQL, so it's not really dynamic. You could query the table to find the columns that sum to 0, use this information to generate COLUMN commands, and then run your SQL. But that's not a very efficient way to do it.

You could use a programming language - such as Perl or Pro*C - to fetch the data into an array, check the array for 0 sums, and then output the appropriate columns.

Ross Leishman
Previous Topic: update a table with values from a different table
Next Topic: problem on declaration section
Goto Forum:

Current Time: Fri Aug 18 20:57:50 CDT 2017

Total time taken to generate the page: 0.17315 seconds