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

Answer 3

I hope following SQL query can also help you

create table myTable (
    ReferenceId int,
    Contract int,
    Tender int,
    Waiver int,
    Quotation int
)
insert into myTable select 1212,1,0,1,0
insert into myTable select 1213,1,1,0,0
insert into myTable select 1214,0,1,0,0
insert into myTable select 1215,1,0,1,0
insert into myTable select 1216,0,0,0,1
insert into myTable select 1217,0,0,1,0
insert into myTable select 1218,0,0,0,0

;with cte as (
    select 
    *,
    ISNULL(NULLIF(
        CONCAT_WS(',',
            case when Contract = 1 then 'Contract' end,
            case when Tender = 1 then 'Tender' end,
            case when Waiver = 1 then 'Waiver' end,
            case when Quotation = 1 then 'Quotation' end
        )
    ,''),'None') 
    as Category
    from myTable
)
select
    ReferenceId, Contract, Tender, Waiver, Quotation, c.val as Category
from cte
cross apply split(Category,',') as c

Here you need a user-defined SQL function SPLIT where the source codes can be found

The output as follows

Answer 4

declare @t table
(
    ReferenceId int,
    Contracts bit,
    Tender bit,
    Waiver bit,
    Quotation bit
)

insert into @t(ReferenceId, Contracts, Tender, Waiver, Quotation)
values
(123, 0, 1, 0, 0), 
(234, 0, 0, 0, 1), 
(345, 0, 0, 0, 0);

select *
from
(
select ReferenceId, 
    Contracts, nullif(Tender, 0) as Tender, nullif(Waiver, 0) as Waiver, nullif(Quotation, 0) as Quotation,
    case when cast(Contracts as int)+Tender+Waiver+Quotation > 0 then cast(null as bit) else cast(0 as bit) end as [None]
from @t 
)as t
unpivot
(
    Flag for Category in (Contracts, Tender, Waiver, Quotation, [None])
) as unpv;

Answer 5

This is ugly, but I don't think it's a bad idea

select *, 'contract' as category 
from t where contract=1 union
select *, 'waiver' 
from t where waiver=1 union
select *, 'tender' 
from t where tender=1 union
select *, 'quotation' 
from t where quotation=1 union
select *, 'none' 
from t where (contract+waiver+tender+quotation)=0
order by referenceid;