CODEWITHSUNDEEP
sqlsql-server
Jennifer Logan
Jennifer Logan

Reputation: 33

SQL logic for the Vlookup function in excel/ How to do a Vlookup in SQL

I have a table with 2 columns OLD_VALUE and NEW_VALUE and 5 rows. 1st row has values (A,B). Other row values can be (B,C),(C,D),(E,D),(D,F). I want to update all the old values with the new value (how a vlookup in excel would work) The Final Result Required: The newest value in the above example would be D,F. i.e. D points to F. E and C point to D. B points to C and A points to B. D pointing to F is the last and newest and there are no more successions after D,F. So (OLD_VALUE,NEW_VALUE)->(A,F), (B,F), (C,F), (D,F), (E,F). I want 5 rows with the NEW_VALUE as 'F'. The level of successions can be ranging from 1 to x.

Upvotes: 0

Views: 488

Answers (2)

cha
cha

Reputation: 10411

This is the table I have used for the script:

declare @t as table(old_value char(1), new_value char(1));
insert into @t values('A','B')
insert into @t values('B','C')
insert into @t values('C','D')
insert into @t values('E','D')
insert into @t values('D','F')

This needs to be done with a recursive CTE. First, you will need to define an anchor for the CTE. The anchor in this case should be the record with the latest value. This is how I define the anchor:

select old_value, new_value, 1 as level 
from @t 
where new_value NOT IN (select old_value from @t)

And here is the recursive CTE I used to locate the latest value for each row:

;with a as(
select old_value, new_value, 1 as level 
from @t 
where new_value NOT IN (select old_value from @t)
union all 
select b.old_value, a.new_value, a.level + 1
from a INNER JOIN @t b ON a.old_value = b.new_value
)
select * from a

Results:

old_value new_value level
--------- --------- -----------
D         F         1
C         F         2
E         F         2
B         F         3
A         F         4

(5 row(s) affected)

Upvotes: 1

ZLK
ZLK

Reputation: 2874

I think a recursive CTE like the following is what you're looking for (where the parent is the row whose second value does not exist as a first value elsewhere). If there's no parent(s) to anchor to, this would fail (e.g. if you had A->B, B->C, C->A, you'd get no result), but it should work for your case:

DECLARE @T TABLE (val1 CHAR(1), val2 CHAR(2));
INSERT @T VALUES ('A', 'B'), ('B', 'C'), ('C', 'D'), ('E', 'D'), ('D', 'F');

WITH CTE AS
(
    SELECT val1, val2
    FROM @T AS T
    WHERE NOT EXISTS (SELECT 1 FROM @T WHERE val1 = T.val2)
    UNION ALL
    SELECT T.val1, CTE.val2
    FROM @T AS T
    JOIN CTE
        ON CTE.val1 = T.val2
)
SELECT *
FROM CTE;

Upvotes: 0

Related Questions