sql - SQL Server 列的瀑布逻辑

Question

你能帮我解决以下问题吗？
我有以下带有标志的数据，我需要以瀑布/级联方式基于这些标志列添加 5 个附加列。我试图用 case 来实现这一点声明，但逻辑变得更加困惑。
这是示例数据以及最终结果的外观。

DECLARE @T AS TABLE
(
    ID          INT,
    Mortality VARCHAR(10),
    Readmission varchar(10),
    EDVisit varchar(10),
    Return_to_OR varchar(10),
    Sepsis varchar(10)
);

DECLARE @endresult AS TABLE
(
    ID          INT,
    Mortality VARCHAR(10),
    Readmission varchar(10),
    EDVisit varchar(10),
    Return_to_OR varchar(10),
    Sepsis varchar(10),
    Indicator1 varchar(15),
    Indicator2 varchar(15),
    Indicator3 varchar(15),
    Indicator4 varchar(15),
    Indicator5 varchar(15)
);


insert into @T VALUES
(1,'Y', 'N', 'Y','Y','Y'), 
(2,'N','Y','N','Y','Y'), 
(3,'N','N','N','Y','Y')

insert into @endresult VALUES
(1,'Y', 'N', 'Y','Y','Y','Mortality','EDVisit','Return_to_OR','Sepsis',null), 
(2,'N','Y','N','Y','Y','Readmission','Return_to_OR','Sepsis',null,null), 
(3,'N','N','N','Y','Y','Return_to_OR','Sepsis',null,null,null)


select * from @T

select * from @endresult

Answer 1

这是一个选项，它使用一些 JSON 和条件聚合。
附注:假设你有一个错字 EDVisit对比 ERVisit 示例或 dbFiddle

select A.*
      ,B.*
 From @T A
 Cross Apply (
                Select Indicator1 = max(case when Seq=1 then [Key] end)
                      ,Indicator2 = max(case when Seq=2 then [Key] end)
                      ,Indicator3 = max(case when Seq=3 then [Key] end)
                      ,Indicator4 = max(case when Seq=4 then [Key] end)
                      ,Indicator5 = max(case when Seq=5 then [Key] end)
                From (
                      Select [Key]
                            ,Value
                            ,Seq = row_number() over (order by (select null))
                        From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                        Where [Key] not in ('ID')
                          and Value<>'N'
                     ) B1
             ) B

返回