Get the scope role for each employee

I have the following query, for each employee I get the list of his different roles :

SELECT e.EmployeeId,r.EntityRoleId, r.Label,er.ValueId
FROM Employee e
LEFT JOIN Employee_Role er ON e.EmployeeId = er.EmployeeId
LEFT JOIN [Function].[Role] r ON er.RoleId = r.EntityRoleId
WHERE e.EmployeeId=54

This is an extract of the previous query output :

I want to get the scope for each label (role) of any employee (here the EmployeeId=54) :

The logic behind ValueId:

• NULL means that we don’t care about the scope because your Role is
  Global (not scoped on a specific Entity)
• 2 in the dbo.Employee_Department table matches “DNA” product.
• 1 in the dbo.Employee_Department table matches “Software Factory”
  department.

Which means that : a role can be linked to any entity in the DB (cf. [SchemaName], [TableName], [ColumnName] columns in [Function].[Role])
In Employee_Role, ValueId matches the PK of the table specified in [Function].[Role].

So to get the label of the role RoleId = 250 for EmployeeId = 5255, get the ValueId=2 and look into the table Product with the Schema HoA and get the record having the PrimaryKey(can change from a table to another) =ValueId = 2, the corresponding Label column matches the Role label as below :

UPDATE

SELECT e.EmployeeId,r.EntityRoleId, r.Label,er.ValueId 
FROM Employee e
LEFT JOIN Employee_Role er ON e.EmployeeId = er.EmployeeId
LEFT JOIN [Function].[Role] r ON er.RoleId = r.EntityRoleId
INNER JOIN sys.indexes i on object_name(i.object_id) = r.TableName 
INNER JOIN sys.tables t on i.object_id = t.object_id and SCHEMA_NAME(t.schema_id) = r.SchemaName
INNER JOIN sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id 
WHERE i.is_primary_key = 1 
ORDER BY e.EmployeeId

I can get the column corresponding to primary key (ValueId)=2, I want to get the content of the column Label existing in the corresponding table which is “DNA” in the screenshot.

My question is how to get the label of each role in this situation?

This can be solved with dynamic SQL. Alternatively, you can convert your resulting row into XML, where each column will be turned into an attribute. This allows you to produce a name/pair values from your row, and filter based upon the column name you were interested in.

Here is a POC, where the contents of the CTE Test would be your row(s) and @Field is the field your are trying to extract. I’ve added a RowNumber field in case multiple rows were returned. Note: I would expect dynamic SQL to be more performant.

DECLARE @Field NVARCHAR(128) = 'Label';

WITH Test AS (
    SELECT 1 a, 2 b, 'greg' Label
    UNION SELECT 2 a, 4 b, 'jan' Label
)



SELECT 
    col.value('.././@RowNumber', 'int') AS RowNumber -- Not needed for single row results
    , col.value('local-name(.)', 'nvarchar(max)') AS [name]
    , col.value('.', 'nvarchar(max)') AS [value] 
FROM
(
    SELECT CAST(
        (
            SELECT 
                *
                , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber -- Not needed for single row results
            FROM 
                Test 
            FOR XML AUTO, ROOT
        ) -- Wrap to treat as a single result
    AS XML) -- convert to XML to wok with it
) D(X)
    CROSS APPLY X.nodes('//@*') N(col) -- Get all Attributes
WHERE col.value('local-name(.)', 'nvarchar(max)') = @field -- limit to the field we care about