Building a view column from individual columns

Question

In our database, we have a system designed to track applications. We have a bool column that indicates whether the application is approved. Then there is another column indicating whether the application is rejected. If none of the columns is true, the application is considered pending.

Is there any simple way to combine them into a single value (for example, in a tinyint view, or maybe a line that says approved, denied, or pending)? Or will it require something like a table function?

UPDATE: It is difficult to choose an answer because they were useful. I will go with Baldi since he published the first.

Answer 1

you can use the case statement in your request: select case approved when 1 then 'Approved' else ...

Case statements can be nested so you can delve into different parameters.

Why not use an int column with three different values, or you can even use a single bool column with a null value. At zero wait, 1 is approved and 0 is denied.

Answer 2

You can use the case statement as follows:

select case when Approved = 1 then 'Approved' when Denied = 1 then 'Denied' else 'Pending' end 'Status' 

Answer 3

Since you save the Approved and Rejected value, you need to worry about the order (which has priority if both are True?). You should definitely put this in a view, so you won't have to repeat this logic later.

Following NTFS permissions, I always prefer Deny to take precedence:

 CASE --Denied has precedence WHEN Denied = 1 THEN 'Denied' WHEN Approved = 1 THEN 'Approved' ELSE 'Pending' END as Status 

Unless you have other requirements that exclude it, I prefer Baldi's suggestion of a null int or check of a tinyint limited column.