SQL-PIVOT-Tabelle mit dynamischem Wert

Ich habe Probleme mit einem Szenario, in dem ich die PIVOT-Tabelle verwenden muss. Diese

Unten finden Sie die Quelldaten. Die erste Quellentabelle enthält Name und Mitarbeiternummer und die zweite Tabelle, die das untergeordnete Element der ersten ist, enthält Hiredate und TermDate mit EmployeeId als ForeignKey

2 Tabellen.

Mitarbeitertabelle

ID     Name  EmployeeNo
----   ----  ----------
1      James   ABC
2      Allen   DEF

Tabelle mit Mitarbeiterdetails

EmployeeId    HireDate      TerminationDate
----------    ---------     ---------------
     1        10/01/2001
     2        12/31/2017     01/01/2019     

erwartetes Ergebnis

JAMES     JAMES HIRE DATE    JAMES TERM DATE     ALLEN    ALLEN HIRE DATE    ALLEN TERM DATE
------    ---------------    ---------------     -----    ---------------    ---------------
ABC        10/01/2001                             DEF      12/31/2017         01/01/2019

Ich konnte die folgende Bestellung einbringen

   JAMES    ALLEN    JAMES HIRE DATE    ALLEN HIRE DATE   JAMES TERM DATE   ALLEN TERM DATE
   ------   -------   ---------------    ---------------   ---------------   --------------- 
     ABC     DEF       10/01/2001          12/31/2017                          01/01/2019

Aber mein Kunde besteht darauf, es zu erledigen

Unten ist der Code, den ich bisher habe

CREATE TABLE #Employees (
    ID BIGINT
    ,[Name] VARCHAR(100)
    ,[EmployeeNo] VARCHAR(100)
    );

CREATE TABLE #EmployeeHireDetails (
    EmployeeId BIGINT
    ,HireDate DATETIME
    ,TermDate DATETIME
    );

INSERT INTO #Employees (ID,[Name],EmployeeNo) VALUES (1,'James','ABC')
INSERT INTO #Employees (ID,[Name],EmployeeNo) VALUES (2,'Allen','DEF')

INSERT INTO #EmployeeHireDetails (EmployeeId,HireDate,TermDate) VALUES (1,DATEADD(year, -2, GETDATE()),NULL)
INSERT INTO #EmployeeHireDetails (EmployeeId,HireDate,TermDate) VALUES (2,DATEADD(year, -1, GETDATE()),DATEADD(month, -5, GETDATE()))

DECLARE @EmpName NVARCHAR(MAX) = NULL
    ,@EmpHireDate NVARCHAR(MAX) = NULL
    ,@EmpTermDate NVARCHAR(MAX) = NULL

SELECT UPPER(E.Name) AS EmployeeName
    ,UPPER(E.Name) + ' ' + 'HIRE DATE' AS EmployeeHireDate
    ,UPPER(E.Name) + ' ' + 'TERM DATE' AS EmployeeTermDate
    ,EmployeeNo
    ,EH.EmployeeId
    ,EH.HireDate
    ,EH.TermDate
INTO #Emps
FROM #Employees E WITH (NOLOCK)
INNER JOIN #EmployeeHireDetails EH WITH (NOLOCK)
    ON E.ID = EH.EmployeeId

SET @EmpName = STUFF((
            SELECT ',' + QUOTENAME(EmployeeName)
            FROM #Emps
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

DECLARE @EmpNameQry NVARCHAR(MAX)  

SET @EmpNameQry = 'SELECT *                 
   FROM (            
   SELECT E.EmployeeNo           
     ,UPPER(E.EmployeeName) EmployeeName            
     --,E.EmployeeId AS EmpName_EmployeeId          
    FROM #Emps E            
    INNER JOIN #Employees EId            
     ON E.EmployeeId = EId.ID               
    ) p            
   Pivot(Max(EmployeeNo) FOR EmployeeName IN (' + @EmpName + ')) AS Pivot_table'

EXEC sp_executesql @EmpNameQry

DECLARE @EmpHireDateQry NVARCHAR(MAX)

SET @EmpHireDate = STUFF((
            SELECT ',' + QUOTENAME(EmployeeHireDate)
            FROM #Emps
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @EmpHireDateQry = 'SELECT *                 
   FROM (            
  SELECT E.HireDate           
    ,EmployeeHireDate               
    FROM #Emps E            
    INNER JOIN #Employees EId            
     ON E.EmployeeId = EId.ID                
    ) p            
   Pivot(Max(HireDate) FOR EmployeeHireDate IN (' + @EmpHireDate + ')) AS Pivot_table2'

EXEC sp_executesql @EmpHireDateQry


DECLARE @EmpTermDateQry NVARCHAR(MAX)

SET @EmpTermDate = STUFF((
            SELECT ',' + QUOTENAME(EmployeeTermDate)
            FROM #Emps
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')


SET @EmpHireDateQry = 'SELECT *                 
   FROM (            
  SELECT E.TermDate           
    ,EmployeeTermDate               
    FROM #Emps E            
    INNER JOIN #Employees EId            
     ON E.EmployeeId = EId.ID                
    ) p            
   Pivot(Max(TermDate) FOR EmployeeTermDate IN (' + @EmpTermDate + ')) AS Pivot_table3'

EXEC sp_executesql @EmpHireDateQry

DROP TABLE #EmployeeHireDetails
DROP TABLE #Employees
DROP TABLE #Emps