Here is one possibility:create table #yourTable (col1 char(1), col2 int)insert #yourTable select 'A', 10 union allselect 'A', 20;with AllVals as ( select a ,n from ( select char(v.number) a from master..spt_values v where type = 'p' and number between 65 and 90 ) alph cross join (select 10 n union all select 20 union all select 30 union all select 40) num )select AllVals.a, AllVals.nfrom AllValsleft join #yourTable yt on yt.col1 = AllVals.a and yt.col2 = AllVals.nwhere yt.col1 is nulland AllVals.a = 'A' --Just the "A"s for this exampledrop table #yourTableoutput:a n---- -----------A 30A 40
Be One with the OptimizerTG