Use T-SQL with conditional logic to override column values ​​for use in SQL Server view

Question

I need to take the data that exists in the current view (it was flattened), and apply some conditional logic to it in line by line to change its presentation. Ideally, this data can be evaluated by users in a view accessible from SSMS 2012 (the database is about Microsoft SQL Server 2012). I am not a T-SQL guru or versed in many of the new features SQL Server has to offer for this. My initial thoughts were to use the cursor and some functions (I am more of a C # developer), but was not sure how this could be accessed through the view (basic requirement). I would prefer not to create new databases in the database (if at all possible).

Below is a sample of current data (As-Is) along with how users would like to display it (To-Be). The following are the rules that affect data output.

As-Is (actual source of data):

PK  MainValue   Signal1 Signal2 Signal3 Signal4 Signal5
1   12345       XYZ     12345   NULL    NULL    NULL    
2   90210       ABC     99999   90210   NULL    NULL    
3   970622      XYZ     88888   34652   970622  NULL


To-Be (how the users would like the data displayed):

PK  MainValue   Signal1 Signal2 Signal3 Signal4 Signal5
1   12345       XYZ     XYZ     XYZ     XYZ     XYZ
2   90210       ABC     99999   99999   99999   99999   
3   970622      XYZ     88888   34652   34652   34652

rules

• Check the field [MainValue]and try to find a match with the first field [Signal#]that we find (always starting with [Signal1]for each row of data.
• As soon as we get a match, we look at the previous signal field (for example, if we get a match between [MainValue]and [Signal3], we will use it [Signal2]as our actual data source).
• Take this data source and now replace all the fields, starting from where we found a match to the final field [Signal5].

. , , , .

Answer 1

nullif coalesce, :

declare @source table (PK int, MainValue varchar(6), Signal1 varchar(6), Signal2 varchar(6), Signal3 varchar(6), Signal4 varchar(6), Signal5 varchar(6))
insert into @source values
(1, '12345', 'XYZ', '12345', null, null, null),
(2, '90210', 'ABC', '99999', '90210', null, null),
(3, '970622', 'XYZ', '88888', '34652', '970622', null)

select 
    PK,
    MainValue, 
    nullif(Signal1, MainValue) as Signal1,
    coalesce(nullif(Signal2, MainValue), nullif(Signal1, MainValue)) as Signal2,
    coalesce(nullif(Signal3, MainValue), nullif(Signal2, MainValue), nullif(Signal1, MainValue)) as Signal3,
    coalesce(nullif(Signal4, MainValue), nullif(Signal3, MainValue), nullif(Signal2, MainValue), nullif(Signal1, MainValue)) as Signal4,
    coalesce(nullif(Signal5, MainValue), nullif(Signal4, MainValue), nullif(Signal3, MainValue), nullif(Signal2, MainValue), nullif(Signal1, MainValue)) as Signal5
from @source

---

, . , :

;with cte (PK, MainValue, Signal1 , Signal2, Signal3, Signal4, Signal5) as 
(
    select 
        PK,
        MainValue,
        nullif(Signal1, MainValue),
        case when nullif(Signal1, MainValue) is null then null else nullif(Signal2, MainValue) end,
        case when nullif(Signal1, MainValue) is null or nullif(Signal2, MainValue) is null then null else nullif(Signal3, MainValue) end,
        case when nullif(Signal1, MainValue) is null or nullif(Signal2, MainValue) is null or nullif(Signal3, MainValue) is null then null else nullif(Signal4, MainValue) end,
        case when nullif(Signal1, MainValue) is null or nullif(Signal2, MainValue) is null or nullif(Signal3, MainValue) is null or nullif(Signal4, MainValue) is null then null else nullif(Signal5, MainValue) end
    from @source
)
select 
    PK,
    MainValue,
    Signal1,
    coalesce(Signal2, Signal1) as Signal2,
    coalesce(Signal3, Signal2, Signal1) as Signal3,
    coalesce(Signal4, Signal3, Signal2, Signal1) as Signal4,
    coalesce(Signal5, Signal4, Signal3, Signal2, Signal1) as Signal5    
from cte