Help with a IF Statement

[nickmarlborough]

Hi, 

My data looks like this:

	Driving Status	School Status	Accounting Status
Driving Test	PAST	(null)	(null)
School Test	(null)	FAIL	(null)
School Test	(null)	PENDING	(null)
Accounting Test	(null)	(null)	COMPLETE

I want it so there is just one column for every test:

	Driving Status	School Status	Accounting Status	TEST STATUS
Driving Test	PAST	(null)	(null)	PAST
School Test	(null)	FAIL	(null)	FAIL
School Test	(null)	PENDING	(null)	PENDING
Accounting Test	(null)	(null)	COMPLETE	COMPLETE

[MatheusC]

Hi, @nickmarlborough 

Perhaps this is empty data and in this case you may be applying the function Len(trim(Field))=0

if(Len(Trim([Driving Status]))=0 and Len(Trim([School Status]))=0,[Accounting Status],

if(Len(Trim([Driving Status]))=0 and Len(Trim([School Status]))<>0,[School Status],[Driving Status])) as [TEST STATUS]

If this information really is null in your data, then you may be using the Isnull(Field) and not(isnull(Field)) function and using the same logic as above.

- Regards, Matheus

[marcus_sommer]

If the not filled field-values are really null() you may simplify the query with:

coalesce(Status1, Status2, Status3, 'my default ...') as FinalStatus

Especially if the empty ones are not NULL or there are in reality more Status fields + values and they may have n results per record it might be helpful to check + transform the values before applying a query, for example by replacing them with numbers and afterwards the check against range-max/avg/sum and so on could give a lot of added value.

[Kushal_Chawda]

@nickmarlborough  You can also use below provided that always one column has value and rest two columns have NULL values(Actual NULL not white space or blank). 

[Driving Status] & [School Status] & [Accounting Status] as Final_Status

[Taofeekat]

If you are sure others column will be Null, then use COALESCE

The coalesce function returns the first of the parameters that has a valid non-NULL representation. 

Syntax:  coalesce(expr1[ , expr2 , expr3 , ...])