Waterfall Logic for SQL Server Columns

Question

Can you please help me with the following.

I have the data with flags as the following and i need to add 5 additional columns based on those flag columns in a waterfall/Cascade way. I tried to accomplish this with case statement but the logic is becoming more confusing.

Here is the sample data and how the end result should look like.

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

user_1570000 · Answer

Here's an option which uses a bit of JSON in concert with a conditional aggregation.

On a side note: Assuming you had a typo EDVisit vs ERVisit

Example or 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

Returns

Waterfall Logic for SQL Server Columns

1 Answers