CODEWITHSUNDEEP
sqlsql-servert-sql
Christopher Kinyua
Christopher Kinyua

Reputation: 170

Combine multiple boolean columns into a single column

I am generation reports from an ERP system where users are provided with a check box which return a boolean value for each item selected. The database is hosted on SQL Server.

However, users can select Contracts with other values as well, as shown below.

enter image description here

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.

enter image description here

Upvotes: 0

Views: 1270

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use apply :

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;

Upvotes: 4

COY
COY

Reputation: 704

I guess a straightforward way is:

select *, 'Contract' as [Category] from [TableOne] where [Contract] = 1
union all select *, 'Tender' as [Category] from [TableOne] where [Tender] = 1
union all select *, 'Waiver' as [Category] from [TableOne] where [Waiver] = 1
union all select *, 'Quotation' as [Category] from [TableOne] where [Quotation] = 1
union all select *, '(none)' as [Category] from [TableOne] where [Contract]+[Tender]+[Waiver]+[Quotation] = 0
order by [Reference ID]

Note that the last line is put there just in case you need to handle the all-zero case.

Upvotes: 1

Related Questions