selecting columns based on condition

Question

selecting columns based on condition

mtz676

SSCrazy Eights

Points: 9217
More actions
February 3, 2021 at 6:46 pm

#3840653

I have 2 tables (1 reference and other actual table)with 30 similar columns (and only 1 row) but with different values. If I have to select only some of the columns and only when a condition is met , what is the easiest approcah to do this.
When I am running this comparision every time my result column count is bound to change, So anywhere between 0 to all 30 columns may show up in my result set every time a comparision happens between the 2 tables.
Ex: Table A
COl 1....Col 30
100
TableB
Col1...Col30
50
TableB Col1 < Col1 in tableA then it shows in result set.If not then it shold not show in the result set.Similarly all 30 columns will be compared every time a comparision happens and any column failing the comparision shows up in the result set.
If ll 30 column values are less than all corresponding 30 columns in the reference table then all 30 column should be part o f the result set.
Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2173 More actions · Answer 1

You could unpivot both tables and inner JOIN them. Here's an example based on another forum question/answer. Something like this

drop table if exists #tTableA
go
create table #tTableA(
  Baseline1      decimal(14,2) not null,
  Baseline2      decimal(14,2) not null,
  Baseline3      decimal(14,2) not null,
  Baseline4      decimal(14,2) not null,
  Baseline5      decimal(14,2) not null,
  Baseline6      decimal(14,2) not null);

insert into #tTableA values(379.5, 282.5, 444.9, 1379.5, 1282.5, 1444.9);

drop table if exists #tTableB
go
create table #tTableB(
  Baseline1      decimal(14,2) not null,
  Baseline2      decimal(14,2) not null,
  Baseline3      decimal(14,2) not null,
  Baseline4      decimal(14,2) not null,
  Baseline5      decimal(14,2) not null,
  Baseline6      decimal(14,2) not null);

insert into #tTableB values(379.5, 282.5, 444.9, 2379.5, 2282.5, 2444.9);

with 
unpvtA_cte as (
    select v.*
    from #tTableA 
         cross apply (values (1, Baseline1), (2, Baseline2), (3, Baseline3),
                             (4, Baseline4), (5, Baseline5), (6, Baseline6)) v(Interval, Baseline)),
unpvtB_cte as (
    select v.*
    from #tTableB 
         cross apply (values (1, Baseline1), (2, Baseline2), (3, Baseline3),
                             (4, Baseline4), (5, Baseline5), (6, Baseline6)) v(Interval, Baseline))
select a.*, b.Baseline as BaselineB
from unpvtA_cte a
     join unpvtB_cte b on a.Interval=b.Interval
where a.Baseline<>b.Baseline;

IntervalBaselineBaselineB
41379.502379.50
51282.502282.50
61444.902444.90

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können