Query to Return each field that is Not Null

Question

Post reply

Query to Return each field that is Not Null

pabond

SSC Rookie

Points: 38
More actions
September 5, 2013 at 9:57 am

#297557

Hello everyone,
I have a database (third party application) that has a table with 30 or so "free fields" that can be customized for our use. I have created some forms that use these free fields and I want to create a query that would show each record where one of the free fields is not NULL. In fact I would like to show all of them so if a record had all 30 free fields as Not Null I would like to have 30 records in the dataset returned. I thought of using a For Loop of some sort but perhaps I am making this more complicated that it needs to be. Basically for every one of these free fields I am using a trigger to create a new task for a user. Any advice or suggestions are greatly appreciated.
Paul

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Not a lot of details to work with here.

Maybe something like this?

select *

from SomeTable

where FreeField1 is not null

or FreeField2 is not null

...

I am not really sure what you are looking for here so that is just a shot in the dark.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pabond SSC Rookie Points: 38 More actions · Answer 2

Sean, sorry for not providing enough detail. My table is rather large with maybe 100 fields. Approximately 30 of these fields are considered "free fields" meaning the application will allow me to use these how I want. There are 15 free text fields, 10 free number fields, 5 free boolean fields, etc. Anyway, what I am trying to accomplish is for each free field that is not NULL a separate record would be returned by the query. So if two of the free text fields were populated with text then my query would return each one separately. See below for more detail.

TABLE

Query would return (as separate records):

ID | Status | Description | Project Number | FreeText01

ID | Status | Description | Project Number | FreeText02

etc...

ScottPletcher SSC Guru Points: 101122 More actions · Answer 3

Something like this may do it for you:

SELECT

tn.ID, tn.Status, tn.Description, tn.[Project Number],

ColNames.ColName

FROM dbo.tablename tn

INNER JOIN (

SELECT 'FreeText01' AS ColName UNION ALL

SELECT 'FreeText02' UNION ALL

SELECT 'FreeText03' --...

) AS ColNames ON

(ColNames.ColName = 'FreeText01' AND tn.FreeText01 IS NULL) OR

(ColNames.ColName = 'FreeText02' AND tn.FreeText02 IS NULL) OR

(ColNames.ColName = 'FreeText03' AND tn.FreeText03 IS NULL)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".