Building a view column out of separate columns

Question

In our database, we have a system set up to keep track of applications. We have a bool column that indicates whether or not the application is approved. Then there's another column that indicates whether or not the application is denied. If neither column is true, then the application is considered to be pending.

Is there any easy way to merge those into one value (like say a tinyint or maybe a string that says "approved", "denied", or "pending") in a view? Or is this going to require something like a Table-valued function?

UPDATE: It's difficult to choose an answer choose since they were all helpful. I'll go with baldy's since he posted first.

Answer 1

you could use a case statement in your query: select case approved when 1 then 'Approved' else ...

Case statements can be nested so you can delve into the different options.

Why not rather use an int column with 3 distinct values, or you can even go as far as using one bool column, with null enabled. When null it is pending, 1 approved and 0 denied.

Answer 2

You can use a case statement like this:

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

Answer 3

Since you're storing both an Approved and a Denied value, you have to worry about ordering (which has precedence if both are True?). You should definitely only put this into a View so you don't have to repeat that logic later.

Following from NTFS permissions, I always prefer Deny to have 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 preclude it, I rather like Baldy's suggestion of a nullable int or check constrained tinyint column.