问题描述
我正在从一个 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;