Query Help - Select one column if its not NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help - Select one column if its not NULL

Author

Topic

aiccucs
Starting Member

7 Posts

Posted - 2010-09-09 : 16:11:20

I want to select one column from a set of columns in a table but the columns will not be the same for each record. See below for an example as I'm having a really hard time explaining this...

In the example below I would like to select the last 'Month' column that isn't NULL for each record in the table.

So for ID 01 I want to select only Month4 and for ID 02 I want to select only Month3.

ID | Products | Month1 | Month2 | Month3 | Month4 |
01 | Example1 |   $5   |  NULL  |   $6   |   $7   |
02 | Example2 |  NULL  |  NULL  |   $10  |  NULL  |

I'm trying to do this without building temp tables as I'm using this inside of an application that is limited but at this point I'll take any method possible.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 16:25:31

Try a CASE statement for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-09 : 16:44:17

Try the COALESCE funtion:

DECLARE @T TABLE 
(
	ID INT, 
	Products VARCHAR(100),
	Month1 MONEY, 
	Month2 MONEY,
	Month3 MONEY, 
	Month4 MONEY
)
INSERT @T
VALUES(01,'Example1',  $5  , NULL ,  $6  ,  $7 ),
(02,'Example2', NULL , NULL ,  $10 , NULL )

SELECT
	ID,
	Products,
	COALESCE(Month4, Month3, Month2, Month1) AS NotNullMonthValue
FROM
	@T

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-09 : 16:45:10

quote:
Originally posted by tkizer

Try a CASE statement for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

LOL better watch out.. Celko will get you for calling the CASE function/expression a statement!! ;)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 16:52:35

Whatever!

Oh and COALESCE is way better, didn't even think of that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

aiccucs
Starting Member

7 Posts

Posted - 2010-09-09 : 17:04:51

quote:
Originally posted by Lamprey

Try the COALESCE funtion:

SELECT
ID,
Products,
COALESCE(Month4, Month3, Month2, Month1) AS NotNullMonthValue
FROM
@T

Worked perfectly. Thanks so much.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-09-10 : 13:14:38

I would think that normalizing your data would be the BEST solution

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

aiccucs
Starting Member

7 Posts

Posted - 2010-09-10 : 16:08:56

quote:
Originally posted by DonAtWork

I would think that normalizing your data would be the BEST solution

I agree, but unfortunately I do not have the access to do that...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources