sql - Get the MotherTongue and the Fluent language of an Employee

Question

I have the following query which gets as result the language spoken by an employee and his corresponding level :

SELECT E.EmployeeId
        ,ISNULL(L.ID ,0) AS LanguageId
        ,L.Label AS Language,
        ll.Label AS LanguageLevel
         FROM Employee e
LEFT JOIN AF_AdminFile aaf ON e.AdminFileId = aaf.AdminFileId
LEFT JOIN AF_Language al ON aaf.AdminFileId = al.AdminFileId
LEFT JOIN Language l ON al.LanguageId = l.ID
LEFT JOIN LanguageLevel ll ON al.LanguageLevelId = ll.LanguageLevelId
ORDER BY e.EmployeeId

The result is like below :

For the Employee with EmployeeId=6,he speaks English/Fluent, Spanish/Good,French/Mother Tongue.
Knowing that I have 187 different languages in my table Language and 4 language levels in my table LanguageLevel (Fair,Fluent,Good,Mother Tongue)

I want to get only the MotherTongue and the Fluent language like below :

EmployeeId MotherTongue        Fluent
6          French              English

Answer 1

The output from your current query follows the pattern of an unnormalized key value store. In this case, the keys are the language levels, and the values the languages. One way to handle this is to aggregate by employee and then use pivoting logic to obtains the languages you want.

SELECT
    e.EmployeeId,
    MAX(CASE WHEN ll.label = 'Mother Tongue' THEN l.label END) AS MotherTongue,
    MAX(CASE WHEN ll.label = 'Fluent' THEN l.label END) AS Fluent
FROM Employee e
LEFT JOIN AF_AdminFile aaf
    ON e.AdminFileId = aaf.AdminFileId
LEFT JOIN AF_Language al
    ON aaf.AdminFileId = al.AdminFileId
LEFT JOIN Language l
   ON al.LanguageId = l.ID
LEFT JOIN LanguageLevel ll
   ON al.LanguageLevelId = ll.LanguageLevelId
GROUP BY
    e.EmployeeId
ORDER BY
    e.EmployeeId;