需要从案例值匹配时开始创建新记录

Question

我知道如果case语句找到该记录的任何第一个匹配项，它将不会检查该记录的其余条件（在我的案例记录2中）。

我也试过UNION，这是给我正确的结果，但有一些额外的记录。任何方法都会为我做。

create table #temp (Name varchar(100), ColA int, ColB int, ColC int, ColD int) 

insert into #temp (name, ColA,ColB, ColC, ColD) 
values ('Name1', 1, null, null, null) 
     ,('Name2', 1, 1 , null, null) 


select name, 
case 
    when ColA = 1 then 'some value' 
    when ColB = 1 then 'Other value' 
    when ColC = 1 then 'some2 value' 
    when ColD = 1 then 'some3 value' 
end CaseValues 
from #temp 

select * from #temp 


/*when you run this case statement I get only two record. But I need three records for each value matched as below: 

    Name CaseValues 

1 Name1 Some value 
2 Name2 some vaule 
3 Name2 other value 
*/ 

Answer 1

select name, 'some value' 
    from from #temp 
where ColA = 1 
union 
select name, 'Other value' 
    from #temp 
where ColB = 1 
union 
... 

Answer 2

喜欢这个？

select name, 'some value' from #temp where colA is not null 
union all 
select name, 'Other value' from #temp where colB is not null 
union all 
select name, 'some2 value' from #temp where colC is not null 
union all 
select name, 'some3 value' from #temp where colD is not null