Need help in creating a query that dismisses empty columns

[royboy75]

Hello,

Suppose I have a table with this format and data in it:

A B C D
X X
X X
X X X
X X

I would like to create a query that will get the results from the table but without columns which are empty.
In the example above, the query will result only in fields A,C and D since B doesn't contain any data.
Also, I will be running this query on tables with different columns structure so the columns names cannot appear in the query.

 

[royboy75]

I have opened it at the SQL forum.
Any answeres please reply here:

http://www.tek-tips.com/viewthread.cfm?qid=1342323&page=1

 

[HarleyQuinn]

You've opened it in the SQL Server forum, if it's an Oracle query it would be best to stay here

Of course, if you're using SQL Server that's going to be the best place for it

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[royboy75]

Actually it's an Oracle, but if someone will give me the idea I'll handle the syntax...
It's just that I've noticed that there the responses are much faster

 

[SantaMufasa]

Roy,

Such a query is possible, but with the restrictions you have embedded in the specifications (i.e., "I will be running this query on tables with different columns structure so the columns names cannot appear in the query"), it makes this far more than a simple Tek-Tips question...the resolution is not for the fainthearted...it is more like a consulting request.

So that our response(s) can build upon efforts that you have already expended, could you please post here your (or SQL Server Forum's) best solution attempt thus far?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[royboy75]

I don't have a clue where to start...

 

[SantaMufasa]

Roy,

Here are some questions that come to my mind (and also throw this request into the realm of a "consulting engagement" versus a simply Tek-Tips question):

Can you offer some context for your request:

a) Can you please explain the business need for this functionality?

b) How many "technical" co-workers compose the (development) environment in which you work...are you "on your own" or are you a member of a team working on this issue?

c) How did you come to receive this assignment? Did you, your team leader, or some other co-worker identify the need for this functionality?

d) Is the resolution to your request targetted as a component of a larger application or does it represent stand-alone functionality?

e) How do you envision this functionality "working" within your application: should this capability act as a FUNCTION, a PROCEDURE, a VIEW, a TEMPORARY TABLE, a FIXED TABLE, a PASS-THROUGH QUERY, et cetera?

Now you can see why I perceive this request more as a "consulting engagement" than a simple Tek-Tips request. If we must resolve your issue here, it could end up taking dozens of posts over multiple days, and I don't know if you, I, or others are ready for that type of a commitment for an "informal, freebie site".



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[royboy75]

Hi,

I don't see how these questions relates to my problem except the last one (I prefer it as a query but any other solution will be OK as well...).
Assume I am working on my own, in my own closed environment, I have 5 different tables that I need to query but I would like to dismiss empty columns...

 

[Dagon]

The requirement seems a little bizarre. If the query can return columns a|b|c|d or a|c|d, how is anyone going to know that the second column is c and not b ? Do you just want to exclude columns which are entirely null or is it just the first non-null value in the row which will be presented ? Are you returning the data as a set of actual columns ? In that case you won't know in advance how many there are going to be, since you don't know how many are going to be excluded. Or do you want to return it as a concatenated string ?

 

[SantaMufasa]

Roy,

I cannot determine how much experience you have, either specifically with Oracle or generally with applications/systems development. I ask the questions, above, to help me understand more about your experience and the infrastructure within which you are working.

My native instincts (coming from 34 years of successful consulting experience) experience tell me that the solution to your problem is non-trivial. Your answers to my questions, above, would have helped convince me that my spending (a non-trivial amount of) time to resolve your need would be a worthwhile investment on my part.

Your response to my inquiry ("I don't see how these questions relates to my problem...") and your suggestion to "Assume I am working on my own, in my own closed environment", lack the context that I require to spend more time with a resolution.

So, although I choose to pass on creating a solution for you, others of my Tek-Tips colleagues may elect to do so.

Good luck,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[royboy75]

>> If the query can return columns a|b|c|d or a|c|d, how is anyone going to know that the second column is c and not b ?
I have the column name in hand don't I?

>> Do you just want to exclude columns which are entirely null or is it just the first non-null value in the row which will be presented ?
I just want to exclude columns which are entirely null.

>> Are you returning the data as a set of actual columns ? In that case you won't know in advance how many there are going to be, since you don't know how many are going to be excluded. Or do you want to return it as a concatenated string ?
>> I am returning the data as a set of actual columns and I can deal with this issue from my Java code, it's not a problem, all I need is the dynamic query.