Query to return only populated columns

Question

Post reply

Query to return only populated columns

liamgavin

SSC Veteran

Points: 227
More actions
April 7, 2003 at 8:05 am

#159645

Hi all,
Am trying to write a query to return only columns that are populated.
If I could do:
select IfNotNull(colX.value), IfNotNull(colY), etc from table
I could go on from there but not sure if this is possible.
Cheers in a dvance
Liam

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 1

Are you saying that you don't want to return rows if there are NULLs or column values?

You can use ISNULL.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

liamgavin SSC Veteran Points: 227 More actions · Answer 2

I only want to return columns that have values for a member, isnull will still return an actual column and heading.

e.g.

select ColX, COlY from table

if COlX is null and ColY = 10 result should be:

COlY

10

cheers

liamgavin SSC Veteran Points: 227 More actions · Answer 3

Is this just not possible????????

At the moment I am using a temp table to store column names and values. It would be ideal if I could use some sort of view, this may be difficult becasue of the dynamic result set.

Help please.

David Burrows SSC Guru Points: 65142 More actions · Answer 4

The only way I can see is to summarise the data to see which columns are null or not null and then create sql from the results and use sp_executesql. Long and messy though

This would exclude the use of a view.

Edited by - davidburrows on 04/15/2003 02:39:25 AM

Far away is close at hand in the images of elsewhere.
Anon.

liamgavin SSC Veteran Points: 227 More actions · Answer 5

Thought this was probably the case - definitely messy!

Have to reference alot of tables for this as basically want to find all member data across DB. Have set up a cursor for this executng some dynamic SQL but the results have to be placed in a table. Will carry on down this route unless anyone can think a better way.

Thanks for your help.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi,

just a thought, though also messy.

Why don't you transpose columns and rows, then check for isnull?

I have seen a script on planet-sourcecode that promised to transpose a table. If you are interested, I will look for the script.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

liamgavin SSC Veteran Points: 227 More actions · Answer 7

Sounds like good idea.

If you could that would be great cheers.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Here we go

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=659&lngWId=5

I haven't tried this, but who knows...

Good luck!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

liamgavin SSC Veteran Points: 227 More actions · Answer 9

Cheers. He seems to have done the same thing as me but in a longwinded fashion. Will stick to my method.

If you want to know what I am doing let me know.

Thanks again.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 10

Did you try the COALESCE function?

BOL 2000:

Returns the first nonnull expression among its arguments.

liamgavin SSC Veteran Points: 227 More actions · Answer 11

Good call and sure I will use this at some point. Problem is I am not sure how many columns I need to return before I run the query and I would be restricted to a certain number of coalesce'd columns. Got me thinking about using some sort of while construct.

Cheers

Andrew Ebert SSC Veteran Points: 230 More actions · Answer 12

If you are using SQL Server 2000 and XML, when you select using OPEN XML, it automagically omits null columns.

This achieves your ends in a simple manner, but I do not know whether it adheres to your means.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 13

What environment ASP, VB, etc. ?

How many records in the result set?

How about a VB DLL, pass parameters to get data to dll and then pass a comma delimited string or XML string back, which SQL pass on to the user.

liamgavin SSC Veteran Points: 227 More actions · Answer 14

Can return it to ASP, VB or Access front ends. Wanted to construct the query in SQL but XML idea looks really good. Only looking to return rows for 1 member in a table so result set is not too large.

Will look into XML solution.

Cheers