Cross apply boolean values in SQL

Question

I had the table below which I need to create a Category column will store the boolean values as a category.

I would like to capture the Categories as a single column and I don't mind having duplicate rows in the view. I would like the first row to return Contract and the second the other value selected, for the same Reference ID.

I achieved his using the query below:

select distinct t.*, tt.category
from t cross apply
     ( values ('Contracts', t.Contracts),
              ('Tender', t.Tender),
              ('Waiver', t.Waiver),
              ('Quotation', t.Quotation)
     ) tt(category, flag)
where flag = 1;

How can I capture an additional Category None where all instances of Contract, Tender, Waiver and Quotation are 0?

Answer 1

None can go right into your VALUES clause. You case use a case expression for the logic. Alternatively, you can use a trick with sign():

select distinct t.*, tt.category
from t cross apply
     ( values ('Contracts', t.Contracts),
              ('Tender', t.Tender),
              ('Waiver', t.Waiver),
              ('Quotation', t.Quotation),
              ('None', 1 - sign(t.Contracts + t.Tender + t.Waiver + t.Quotation))
     ) tt(category, flag)
where flag = 1;

I am guessing that you don't have duplicates in your original table, so you should dispense with SELECT DISTINCT.

Answer 2

Maybe something like this:

select distinct t.*, tt.category
from t cross apply
     ( values ('Contracts', t.Contracts),
              ('Tender', t.Tender),
              ('Waiver', t.Waiver),
              ('Quotation', t.Quotation),
              ('None', -1)
     ) tt(category, flag)
where flag = 1 or 
(Contracts = 0 and Tender = 0 and Waiver = 0 and Quotation = 0 and flag = -1);

Here's my sample fiddle (guessing you have bit fields, but it works with int fields too): http://sqlfiddle.com/#!18/9f8e7/1