tmp, Sql Server - rextester

Language: Layout:

WITH ComboValues AS
(
    SELECT 1 AS id, 'Department' AS Fieldname, 'IT' AS FieldValue, 1 AS CompanyId UNION ALL
    SELECT 2 AS id, 'Department' AS Fieldname, 'HR' AS FieldValue, 1 AS CompanyId UNION ALL
    SELECT 3 AS id, 'Category' AS Fieldname, 'Staff' AS FieldValue, 1 AS CompanyId UNION ALL
    SELECT 4 AS id, 'Category' AS Fieldname, 'Manager' AS FieldValue, 1 AS CompanyId UNION ALL
    SELECT 5 AS id, 'Department' AS Fieldname, 'IT' AS FieldValue, 2 AS CompanyId UNION ALL
    SELECT 6 AS id, 'Department' AS Fieldname, 'HR' AS FieldValue, 2 AS CompanyId UNION ALL
    SELECT 7 AS id, 'Category' AS Fieldname, 'Staff' AS FieldValue, 2 AS CompanyId UNION ALL
    SELECT 8 AS id, 'Category' AS Fieldname, 'Manager' AS FieldValue, 2 AS CompanyId

)
, Employee AS
(
    SELECT 'Pratik' AS FName,  1 AS id, 1 AS Department, 4 AS catid, 1 AS CompanyId UNION ALL
    SELECT 'Praveen' AS FName, 2 AS id, 2 AS Department, 3 AS catid, 1 AS CompanyId UNION ALL
    SELECT 'Nilesh' AS FName,  3 AS id, 2 AS Department, 3 AS catid, 1 AS CompanyId 
)
,tmp_table
AS
(
    SELECT o.id AS id_old, n.id AS id_new
    FROM combovalues o
    INNER JOIN combovalues n 
    ON o.fieldname = n.fieldname AND o.fieldvalue = n.fieldvalue
    WHERE o.companyid = 1 AND n.companyid = 2
)
SELECT fname, d.id_new, c.id_new, 2
FROM employee e
LEFT JOIN tmp_table d
ON e.department = d.id_old
LEFT JOIN tmp_table c
ON e.catid = c.id_old
WHERE companyid = 1;

View schema
Execution time: 0,02 sec, rows selected: 3, rows affected: 0, absolute service time: 0,23 sec, absolute service time: 0,23 sec