Search code examples
sql-servervlookuphierarchical-data

Lookup values of 1 column into another and if exists store the value in 3rd column

I am trying to achieve a hierarchy in a single row inside a table

I have a table with 2 main columns, Emp(Employee) and Mgr(Manager). I need to do a vlookup(eqivalent in SQL Server) of the mgr value and check that in the emp list and if exists, I need to place the mgr value of the match in MH1. Then vlookup(equivalent in SQL Server) MH1 again in the emp list and if matched place its corresponding mgr value in MH2 and so on.. until there are no mgr value matches. Below is the image of the sample result

enter image description here

Emp     Mgr     MH1  MH2    MH3
Mark    Thomas  Bob  Kim    Tim
Robert  Clain   Barry      Murray   
Chris   Crain   Kale       Kelvin   
Andrew              
Thomas  Bob         
Clain   Barry           
Crain   Kale            
Murray  Tom         
Bob Kim         
Kim Tim         
Kale    Kelvin          
Barry   Murray
Solution

  • Method 1: Use recursive cte. It is fast but you need an unbroken hierarchy relationship in your sample input table to get a desirable output:

    WITH CTE(HierarchyStr, Emp, Mgr) 
AS (
    SELECT CAST(Emp AS nvarchar(1000)), Emp, Mgr 
        FROM T where Mgr IS NULL
    UNION ALL
    SELECT CAST(T.Emp + ', ' + CTE.HierarchyStr AS nvarchar(1000)), T.Emp, T.Mgr 
        FROM T JOIN CTE on T.Mgr = CTE.Emp  
)
SELECT 
    HierarchyStr
FROM 
    CTE;

    Method 2: This pretty intuitive TSQL may better fit your required output but it will be slow on large tables, use it with care:

    DECLARE @MhColName varchar(10) = 'Mgr';
DECLARE @OldMhColName varchar(10);
DECLARE @I int = 0;
DECLARE @Done bit = 0;
DECLARE @CheckSql nvarchar(1000);

SELECT Emp, Mgr INTO #ResultTable FROM T;

WHILE (@Done = 0)
BEGIN
    SET @OldMhColName = @MhColName;
    SET @I = @I + 1;
    SET @MhColName = 'MH' + CAST(@I AS varchar(2));

    EXEC('ALTER TABLE #ResultTable ADD ' + @MhColName + ' nvarchar(30)');

    EXEC('UPDATE R1 SET ' + @MhColName + ' = R2.Mgr 
            FROM 
                #ResultTable R1, #ResultTable R2
            WHERE R1.' + @OldMhColName + ' = R2.Emp');

    SET @CheckSql = 
         'IF NOT EXISTS(SELECT * FROM #ResultTable WHERE ' + @MhColName + ' IS NOT NULL)
          BEGIN
            ALTER TABLE #ResultTable DROP COLUMN ' + @MhColName + ';
            SET @Done = 1;
          END';
    EXECUTE sp_executesql @CheckSql, N'@Done bit OUTPUT', @Done = @Done OUTPUT;
END;

SELECT * FROM #ResultTable;

    Sample Input:

    Emp     Mgr
Mark    Thomas
Thomas  Bob
Bob     Kim
Kim     Tim
Tim     Kelvin
Andrew  NULL
Kelvin  Andrew

    Method 1 Output:

    HierarchyStr
Andrew
Kelvin, Andrew
Tim, Kelvin, Andrew
Kim, Tim, Kelvin, Andrew
Bob, Kim, Tim, Kelvin, Andrew
Thomas, Bob, Kim, Tim, Kelvin, Andrew
Mark, Thomas, Bob, Kim, Tim, Kelvin, Andrew

    Method 2 Output:

    Emp     Mgr     MH1     MH2     MH3     MH4     MH5
Mark    Thomas  Bob     Kim     Tim     Kelvin  Andrew
Thomas  Bob     Kim     Tim     Kelvin  Andrew  NULL
Bob     Kim     Tim     Kelvin  Andrew  NULL    NULL
Kim     Tim     Kelvin  Andrew  NULL    NULL    NULL
Tim     Kelvin  Andrew  NULL    NULL    NULL    NULL
Andrew  NULL    NULL    NULL    NULL    NULL    NULL
Kelvin  Andrew  NULL    NULL    NULL    NULL    NULL