Replace Union All with Or Sql Server

Question

I have below table with some sample data

Create table dbo.Test_2020
(
 id int identity(1,1),
 level_cd_1 varchar(10) null,
 level_cd_2 varchar(10) null
)

insert into dbo.Test_2020

select 'cd_1_01',null
union all
select 'cd_1_02',null
union all
select 'cd_1_03','cd_2_01'
union all
select null, 'cd_2_02'
union all
select null, 'cd_2_03'
union all
select 'cd_1_04', 'cd_2_04'

And below is the query using to get not null values from both columns: level_cd_1 & level_cd_2

 select id, level_cd_1 as level_cd from Test_2020 where level_cd_1 is not null
 union all
 select id, level_cd_2  from Test_2020 where level_cd_2 is not null

Question is can i achieve the same result using OR condition instead querying the same table twice, here is the query i tried to write but not returning the same result set as that of above query

 select id, coalesce(level_cd_1,level_cd_2)as leve_cd from Test_2020 where 
  (level_cd_1 is not null or level_cd_2 is not null)

Let me know if this is possible.

user_1570000 · Answer

How about with a CROSS APPLY

Example

Select A.ID
      ,B.*
 From  Test_2020 A
 Cross Apply ( values ( level_cd_1 )
                     ,( level_cd_2 )
             ) B(level_cd)
 Where level_cd is not null

Returns

ID  level_cd
1   cd_1_01
2   cd_1_02
3   cd_1_03
3   cd_2_01
4   cd_2_02
5   cd_2_03
6   cd_1_04
6   cd_2_04

Replace Union All with Or Sql Server

1 Answers