CODEWITHSUNDEEP
sql-server-2008case
GPH
GPH

Reputation: 1897

conditional CASE WHEN statement

I have a table with the below structure:

CustID | Number 1  | Number 2 | Number 3  | Number 4
    1  | 072454584 |          | 017726593 | 
    2  |           |0125456852|           | 0125785448

I'm trying to do a query that selects the first number that is available, so if using customer ID 2 it would return only number 2, if there was a record with only number 4 present it would ignore 1,2,3. I've tried doing a case when statement but I cant seem to work out the logic.

Upvotes: 0

Views: 113

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460058

You can use COALESCE which returns the first non-null value:

SELECT COALESCE([Number 1],[Number 2],[Number 3], [Number 4]) AS FirstNonNullNum
FROM dbo.Table1
WHERE CustID = @paramID

Demo

However, your model seems to be semi optimal. If you have columns Number 1 - Number N you shoudld better normalize it and use a separate table instead of columns. That makes all queries simpler and far more efficient. It's also much more maintainable and less error-prone if you plan to add more columns.

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

In case you have NULL values in those columns then use COALESCE:

SELECT CUSTID, COALESCE(number1, number2, number3, number4)

Upvotes: 1

Related Questions