V Lookup in SQL

Question

Hi All,

Is there a SQL Equivalent of the VLookup Function in Excel? Or can some one tell me about the logic using which I can accomplish this?

Thanks

Answer 1

"Vlookups" can be done with a (correlated) subquery...  Just depends on what you're trying to do...

Do you have an example that you'd like to share with us?

Answer 2

I have a table which looks something like this,

ID        NAME          LEVEL
=======================
1        ABC                     0
2        BCG                    1
3        CDE                    1
4        DEF                    1
5        SED                    2
6        BAT                    2
7        CAR                    2

I want to move this to another table which looks something like this:

ID       NAME       LEVEL0        LEVEL1        LEVEL2
=======================================
1         ABC         ABC   
2         BCG        ABC                BCG  
3         CDE        ABC                CDE 
4         DEF         ABC                DEF 
5         SED         ABC               BCG               SED 
6         BAT          ABC              CDE                 BAT
7        CAR          ABC              DEF                 CAR

Thanks

Answer 3

I don't understand how name = 'SED' belongs to 'BCG' and not 'CDE' or 'DEF'...

Since 'SED' is at level2, does it belong to all level 1s?

Answer 4

Here's one way to answer your question...  Use union all statements, provided the number of levels is fixed and not constantly changing.

INSERT INTO [test_vlookup] VALUES (1,'ABC',0)
INSERT INTO [test_vlookup] VALUES (2,'BCG',1)
INSERT INTO [test_vlookup] VALUES (3,'CDE',1)
INSERT INTO [test_vlookup] VALUES (4,'DEF',1)
INSERT INTO [test_vlookup] VALUES (5,'SED',2)
INSERT INTO [test_vlookup] VALUES (6,'BAT',2)
INSERT INTO [test_vlookup] VALUES (7,'CAR',2)

SELECT id, name, name as level0, '' as level1, '' as level2 FROM test_vlookup WHERE level = 0

union all

SELECT id, name, (select distinct name from test_vlookup where level = 0) as level0, name as level1, '' as level2 FROM test_vlookup WHERE level = 1

union all

SELECT id, b.name, (select distinct name from test_vlookup where level = 0) as level0,
a.name as level1, b.name as level2 FROM (select distinct name from test_vlookup where level = 1) a, test_vlookup b WHERE level = 2


id   name   level1 level2 level3
1    ABC    ABC       
2    BCG    ABC    BCG   
3    CDE    ABC    CDE   
4    DEF    ABC    DEF   
5    SED    ABC    BCG    SED
6    BAT    ABC    BCG    BAT
7    CAR    ABC    BCG    CAR
5    SED    ABC    CDE    SED
6    BAT    ABC    CDE    BAT
7    CAR    ABC    CDE    CAR
5    SED    ABC    DEF    SED
6    BAT    ABC    DEF    BAT
7    CAR    ABC    DEF    CAR

Answer 5

Note: If my previous post is how it's supposed to be and you need a unique id in the result set, then my query will have to be rewritten.

Answer 6

Your data makes no sense, there's no way to get the output you want from the input you gave - why wouldn't these rows be in the output too?

ID       NAME       LEVEL0        LEVEL1        LEVEL2
=======================================
5         SED         ABC               CDE               SED 
5         SED         ABC               DEF               SED 
6         BAT          ABC              BCG                 BAT
6         BAT          ABC              DEF                 BAT
7        CAR          ABC              BCG                 CAR
7        CAR          ABC              CDE                 CAR

Answer 7

I have a better way of doing this, but first does the output from this look right?

Dave

set nocount on

declare @mock table
   (    id          integer     not null    primary key,
        [name]      varchar(5)  not null,
        [level]     integer     not null
   )

insert into @mock values (1, 'ABC', 0)
insert into @mock values (2, 'BCG', 1)
insert into @mock values (3, 'CDE', 1)
insert into @mock values (4, 'DEF', 1)
insert into @mock values (5, 'SED', 2)
insert into @mock values (6, 'BAT', 2)
insert into @mock values (7, 'CAR', 2)
insert into @mock values (8, 'JBC', 3)
insert into @mock values (9, 'JED', 3)
insert into @mock values (10,'JQ1', 3);
--select * from @mock;

declare @maxLevel integer
set @maxLevel = (select max([level]) from @mock);

with vLookup
as
( select id,
         0 as [level],
         cast (1 as int) as Seq,
         [name],
         [name] as associatedName
    from @mock
  where [level] = 0
  union all
  select a.id,
         a.[level],

         cast (row_number () over
         ( partition by a.[level]
           order by b.id
         ) as integer) as Seq,
         b.[name],
         a.[name] as associatedName
    from @mock a
   inner join small_iterator (nolock) i
      on i.iter <= @maxLevel
   inner join vLookup b
      on a.[level] = b.[level] + i.iter
     and b.[level] = 0
)

select id,
       [name],
       v.seq,
       v.[level],
       [name] as level0,
       isnull (( select max(l1.associatedName) from vLookup l1
         where l1.[level] = 1
           and v.seq = l1.seq
           and v.level >= 1
       ), '') as level1,
       isnull (( select max(l2.associatedName) from vLookup l2
         where l2.[level] = 2
           and v.seq = l2.seq
           and v.level >= 2
       ), '') as level2,
       isnull (( select max(l3.associatedName) from vLookup l3
         where l3.[level] = 3
           and v.seq = l3.seq
           and v.level >= 3
       ), '') as level3,
       isnull (( select max(l4.associatedName) from vLookup l4
         where l4.[level] = 4
           and v.seq = l4.seq
           and v.level >= 4
       ), '') as level4
  from vLookup v

-- ------------  Sample Output:  ------------

--   id          name  seq         level       level0 level1 level2 level3 level4
--   ----------- ----- ----------- ----------- ------ ------ ------ ------ ------
--   1           ABC   1           0           ABC                        
--   2           ABC   1           1           ABC    BCG                 
--   3           ABC   2           1           ABC    CDE                 
--   4           ABC   3           1           ABC    DEF                 
--   5           ABC   1           2           ABC    BCG    SED          
--   6           ABC   2           2           ABC    CDE    BAT          
--   7           ABC   3           2           ABC    DEF    CAR          
--   8           ABC   1           3           ABC    BCG    SED    JBC   
--   9           ABC   2           3           ABC    CDE    BAT    JED   
--   10          ABC   3           3           ABC    DEF    CAR    JQ1   

Answer 8

This runs a little better and doesn't have the disadvantage of requiring the iterator table:

set nocount on

declare @mock table
   (    id          integer     not null    primary key,
        [name]      varchar(5)  not null,
        [level]     integer     not null
   )

insert into @mock values (1, 'ABC', 0)
insert into @mock values (2, 'BCG', 1)
insert into @mock values (3, 'CDE', 1)
insert into @mock values (4, 'DEF', 1)
insert into @mock values (5, 'SED', 2)
insert into @mock values (6, 'BAT', 2)
insert into @mock values (7, 'CAR', 2)
insert into @mock values (8, 'JBC', 3)
insert into @mock values (9, 'JED', 3)
insert into @mock values (10,'JQ1', 3);
--select * from @mock;

declare @maxLevel integer
declare @baseId   integer
declare @basename varchar (5)
set @maxLevel = (select max([level]) from @mock);

select @baseId = id,
       @baseName = [name]
  from @mock
where level = 0

select a.id,
       a.[name],
       @baseName as level0,
       max (case when b.[level] = 1 then b.[name] else '' end) as level1,
       max (case when b.[level] = 2 then b.[name] else '' end) as level2,
       max (case when b.[level] = 3 then b.[name] else '' end) as level3,
       max (case when b.[level] = 4 then b.[name] else '' end) as level4
  from @mock a
 inner join
     (
       select id,
              [name],
              [level],
              row_number () over
              ( partition by [level]
                order by id
              ) as seq
         from @mock
     ) b
    on a.level >= b.level
group by a.id, b.seq, a.[name]
having a.name = max (case when b.[level] = 1 then b.[name] else '' end)
    or a.name = max (case when b.[level] = 2 then b.[name] else '' end)
    or a.name = max (case when b.[level] = 3 then b.[name] else '' end)
    or a.name = max (case when b.[level] = 4 then b.[name] else '' end)
    or a.name = @baseName
order by a.id

-- ------------  Sample Output:  ------------

--   id          name  level0 level1 level2 level3 level4
--   ----------- ----- ------ ------ ------ ------ ------
--   1           ABC   ABC                        
--   2           BCG   ABC    BCG                 
--   3           CDE   ABC    CDE                 
--   4           DEF   ABC    DEF                 
--   5           SED   ABC    BCG    SED          
--   6           BAT   ABC    CDE    BAT          
--   7           CAR   ABC    DEF    CAR          
--   8           JBC   ABC    BCG    SED    JBC   
--   9           JED   ABC    CDE    BAT    JED   
--   10          JQ1   ABC    DEF    CAR    JQ1