WITH temptb ( ValueType, Num )
AS ( SELECT 'E' AS ValueType ,
0 AS Num
UNION
SELECT 'V' AS ValueType ,
1 AS Num
UNION
SELECT 'H' AS ValueType ,
2 AS Num
)
SELECT HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID ,
HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID ,
HYS_UserEmployeeFieldAccess.EmployeeFieldLabel ,
( SELECT Tem.ValueType
FROM temptb AS Tem
WHERE Num = MIN(T.Num)
) AS EmployeeFieldAccessType
FROM HYS_UserEmployeeFieldAccess
INNER JOIN temptb AS T ON HYS_UserEmployeeFieldAccess.EmployeeFieldAccessType = T.ValueType
WHERE ( UserId IN ( SELECT UserRoleId
FROM HYS_UserRoleLink
WHERE UserId = 27 )
OR UserId = 27
)
GROUP BY dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeParamID ,
dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldEntityTypeID ,
dbo.HYS_UserEmployeeFieldAccess.EmployeeFieldLabel