oracle query help

I am looking for Oracle query with following requirements

- Find all columns where data is present

col1	        col2	        col3	       col4	      col5	      col6

								aa		x
aw		dsa		ds						y
						as				z	

Open in new window


OUTPUT

x col5
y col1,col2,col3
z col4

Open in new window

LVL 5
CalmSoulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
select col6, case when col1 <> '' then 'col1, ' else '' end ||
                    case when col2 <> '' then 'col2, ' else '' end ||
                    case when col3 <> '' then 'col3, ' else '' end ||
                    case when col4 <> '' then 'col4, ' else '' end ||
                    case when col5 <> '' then 'col5, ' else '' end  "Cols"
from <table>
where
col1 <> '' or
col2 <> '' or
col3 <> '' or
col4 <> '' or
col5 <> ''

note you may need to wrap the columns in isnull  if they are nulls and not blanks.
0
CalmSoulAuthor Commented:
My data has nulls and blanks? where the wapper should go?
0
slightwv (䄆 Netminder) Commented:
Just use the syntax above which is pretty much the same thing I provided in your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28375718.html

No need for a wrapper.  Just remove the where clause above.

Just like the code above and taken from your previous question (setup for tables is still in there):
select mytag, 
	rtrim(
		case when store1 is not null then 'store1,' end || 
		case when store2 is not null then 'store2,' end || 
		case when store3 is not null then 'store3,' end || 
		case when store4 is not null then 'store4,' end || 
		case when store5 is not null then 'store5' end 
	,',')
	mycsv
from tab1
/ 

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kyle AbrahamsSenior .Net DeveloperCommented:
Slight,
I'm assuming if a row has no data then he doesn't want to see said row.
select col6, rtrim(case when nvl(col1,'') <> '' then 'col1, ' else '' end || 
                    case when nvl(col2,'')<> '' then 'col2, ' else '' end ||
                    case when nvl(col3,'')<> '' then 'col3, ' else '' end ||
                    case when nvl(col4,'')<> '' then 'col4, ' else '' end ||
                    case when nvl(col5,'')<> '' then 'col5, ' else '' end ,',') "Cols"
from <table>
where 
nvl(col1,'') <> '' or 
nvl(col2,'') <> '' or 
nvl(col3,'') <> '' or 
nvl(col4,'') <> '' or 
nvl(col5,'') <> '' 

Open in new window

0
CalmSoulAuthor Commented:
thanks Kyle, let me see your solution
0
slightwv (䄆 Netminder) Commented:
>> then he doesn't want to see said row.

Unfortunately "nvl(col1,'') <> '' or ""  will not work in Oracle.

An empty string is considered a null value and nothing can be '=' or '!=' to a null.  You MUST use 'is null' and 'is not null'.

Try it:
select 'hello' from dual where nvl(null,'') = '';


If they have that requirement, personally I would do:
when coalesce(col1,col2,col3,col4,col5) is not null
0
Kyle AbrahamsSenior .Net DeveloperCommented:
NVL converts the null to an empty string making it comparable . . . same thing as

isnull(column, '') != ''

no?
0
slightwv (䄆 Netminder) Commented:
>>no?

No.   Try it from what I posted with the DUAL table.

NVL converts a null to an actual value.  An empty string is the same thing as a null in Oracle and not an actual value.

Oracle Database treats a character value with a length of zero as null. However, do not use null to represent a numeric value of zero, because they are not equivalent.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements005.htm#SQLRF30037
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Alright I stand corrected (it's been a few years since I worked with oracle),

change the

NVL (XX, '') <> ''
to NVL(XX, ' ') <> ' '


if there are more than one blank you may have to do:
/*Translate 2 or more blanks to one blank */
NVL( REGEXP_REPLACE(XX,'( ){2,}', ' '), ' ') <> ' '



As the original poster has said there are both null and blanks so just worrying about the null is not enough.
0
slightwv (䄆 Netminder) Commented:
>>As the original poster has said there are both null and blanks so just worrying about the null is not enough.

Then "to NVL(XX, ' ') <> ' '"  will return a column with '           '.  Still not the requirement?

If you want to treat spaces in a column as 'no data' and a null then:
when coalesce(trim(col1),trim(col2),trim(col3),trim(col4),trim(col5)) is not null

regexp calls are very expensive and should only be used when necessary.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.