Replacing Id with Actual values in SQL Server

Question

I have the following table structure also I have mention my expected output please help me with query as I don't know much about SQL query

Query :

SELECT * FROM(
SELECT ESIDispensary,ESILocation,test,Category, COUNT(*) AS [Total Count]
FROM
    (SELECT category,ESILOCATION,ESIDISPENSARY,TEST 
    FROM(SELECT id,CompanyId,FName,Code,category,ESILOCATION,ESIDISPENSARY
         FROM dbo.[EmployeeDetail] e  WHERE e.CompanyId = 1 AND Category in (1,2)) a 
    LEFT JOIN 
    (SELECT * 
     from 
        (SELECT EmployeeId, CustomeFieldName,FieldValue 
         FROM dbo.[CustomeFieldDetail] C  
         JOIN dbo.[EmployeeDetail]  e ON e.id = c.employeeid AND e.CompanyId = c.companyid  
         WHERE e.CompanyId = 1 AND Category IN (1,2)) SRC 
PIVOT 
(MAX(FieldValue) FOR CustomeFieldName IN([TEST])) 
piv)  
b ON a.Id = b.EmployeeId 
) AS a
GROUP BY  ESIDispensary ,ESILocation,test,Category) x

Table generated using above query

ESIDispensary   ESILocation test    Category    Count
12                11        NULL       1        NULL
12                13        30         1        1
14                13        29         2        2 

Table 1 : ESI

Id  CompanyId   FieldName        ComboValue 
11      1       ESILOCATION        mumbai   
12      1       ESIDISPENSARY      mumbai   
13      1       ESILOCATION        pune     
14      1       ESIDISPENSARY      pune     
29      1       TEST               HDFC     
30      1       TEST               ICICI    

Table 2 : Category

id   CategoryName
1     staff
2     manager

Problem is i want to replace IDs with respected values also can i change above query to get expected result

Expected Summary Output :

ESIDispensary   ESILocation test      staff     manager
mumbai           mumbai     NULL       1        NULL
mumbai           pune       ICICI      1        1
pune             pune       HDFC      NULL      2 

Answer 1

Is this is what you want:

SELECT (SELECT ComboValue FROM ESI WHERE ID = ESIDispensary) AS  ESIDispensary,
            SELECT ComboValue FROM ESI WHERE ID = ESILocation) AS  ESILocation,
            SELECT ComboValue FROM ESI WHERE ID = test) AS  test,
            Category, [Total Count]
    FROM
    (
        SELECT ESIDispensary,ESILocation,test,Category, COUNT(*) AS [Total Count]
        FROM
            (SELECT category,ESILOCATION,ESIDISPENSARY,TEST 
            FROM(SELECT id,CompanyId,FName,Code,category,ESILOCATION,ESIDISPENSARY
                 FROM dbo.[EmployeeDetail] e  WHERE e.CompanyId = 1 AND Category in (1,2)) a 
            LEFT JOIN 
            (SELECT * 
             from 
                (SELECT EmployeeId, CustomeFieldName,FieldValue 
                 FROM dbo.[CustomeFieldDetail] C  
                 JOIN dbo.[EmployeeDetail]  e ON e.id = c.employeeid AND e.CompanyId = c.companyid  
                 WHERE e.CompanyId = 1 AND Category IN (1,2)) SRC 
        PIVOT 
        (MAX(FieldValue) FOR CustomeFieldName IN([TEST])) 
        piv)  
        b ON a.Id = b.EmployeeId 
    ) AS a
    GROUP BY  ESIDispensary, ESILocation, test, Category) x