This is a little tough, Hope some help is availabl

Question

This is a little tough, Hope some help is availabl

scrocker

SSC Veteran

Points: 231
More actions
December 7, 2002 at 8:07 pm

#159035

I could use a little help with this one.
I have a VB application that uses a access 2000 database, what I need to do is list out each row but only what is not null So here is an example of what I need accomplished.
Simulated Table in Access 2000
------------------------------------------------------------------------------
V_CH V_Code1 V_Code2 V_Price V_Price2 V_Choice
1 TRBLX 19.95 3
1 TRBLZ TRBLN 7.95 6.85 4
1 ZDNTV 12.95 3
-------------------------------------------------------------------------------
Desired Results
1 TRBLX 19.95 3
1 TRBLZ TRBLN 7.95 6.85 4
1 ZDNTV 12.95 3
Does anyone out there know how to do this. Granted this table is much longer and I need to search every column in the row and only return what is not null.
If someone out there knows how to do this in T-SQL (Cause it's going into a VB application) I would greatly appreciate it
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

When you say only return what is not null, what do you mean? Do you mean if a particular field for a particular row is not null return that field? The example you gave and what is returned look identical.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

scrocker SSC Veteran Points: 231 More actions · Answer 2

Kinda, What i need is to print only what is not null in the row or Column and output that to either an Excel Spreadsheet or bookmarks in Word, I have been fighting this thing for a while,

So If I have the following table

V_CH V_Code1 V_Code2 V_code3 V_price1 V_price2 V_Price3

1 Test 19.95

If I run the following sql statement

Select * from Table

I would get

1 test 19.95

I am trying to find a way to put these into more of a text string so they look like this

1 Test 19.95 Without all of the Null's between

Hopefully that make some more since

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Probably the easiest way is to parse everything in VB, it's not terribly difficult.

If you just want the first non-zero value, however, look at the coalesce function in T-SQL. I often use it in a query like:



SELECT COALESCE(PreferredName, FirstName) EmployeeName

FROM dbo.Employee

If the PreferredName is null, I'll pull the first name. If the PreferredName isn't null, however, I won't see the first name at all. That's part of the power of the coalesce function.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

scrocker SSC Veteran Points: 231 More actions · Answer 4

Thanks bkelly, I was considering using that function but wasn't totally sure how it worked I will play with that for a while. It's been last thing we have to do for this application and the guy who built the DB didn't know alot about DB's, cause pretty much broke every rule and there is very little Normal forms.

And if you change the dbase tables, it breaks the application 🙁

Thanks

Will give it a shot and that you for all of the help

Antares686 SSC Guru Points: 125444 More actions · Answer 5

But the problem I see with COALESCE is yuo example requires all non-null fields concatinated as a string to get all the non-null resutls. However you don't think that looks odd as columns no longer line up?

Anyway try this as well.

TSQL

SELECT (ISNULL(V_CH + ' ','') + ISNULL(V_Code1 + ' ','') + ISNULL(V_Code2 + ' ','') + ISNULL(V_Price + ' ','') +

ISNULL(V_Price2 + ' ','') + ISNULL(V_Choice,'')) AS Desired_Results FROM tblX

The reason I used ISNULL(COL + ' ' is becaus if COL is NULL then + ' ' will produce NULL. But if not then you have a spcae dividing you data.

One other thing that bugs me is you stated Access 2000 DB. If it is an Access DB and not SQL then you are actually using JET SQL syntax and the above would be

SELECT IIf(IsNull([TblX].[V_CH] + " "),"",[TblX].[V_CH] + " ") +....

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 6

SQL doesn't have "variable length rows" so you need to return something for each column. Use

select ColumnA=isNull(columnA, '') and specify every column and an appropriate default value. If you are not doing computation with any of the numeric columns, you can convert them all to character format first

Select ColA=(isNull(cast(ColA as varchar(255), ''))