将多个布尔列合并为一列

问题描述

我正在从一个 ERP 系统生成报告，其中为用户提供了一个复选框，该复选框为所选的每个项目返回一个布尔值.数据库托管在 SQL Server 上.

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.

我想将类别捕获为单列，并且我不介意视图中有重复的行.对于相同的参考 ID，我希望第一行返回 Contract，第二行返回选择的其他值.

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.

推荐答案

您可以使用 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;