score:0

Use below CROSS APPLY for your query

SELECT  [t].*,
        [DTNameDescription].[Description] AS [Name]
        [DTInfoDescription].[Description] AS [Info] 
FROM    [TrainerClass] AS [t] 
        CROSS APPLY (
            SELECT  [Description] 
            FROM    [DescriptionTranslation] AS [d] 
            WHERE   [t].[Code] = [d].[Code] 
                        AND [d].[FieldName] = 'Name'
        ) AS DTNameDescription
        CROSS APPLY (
            SELECT  [Description] 
            FROM    [DescriptionTranslation] AS [d] 
            WHERE   [t].[Code] = [d].[Code] 
                        AND [d].[FieldName] = 'Info'
        ) AS DTInfoDescription
WHERE   [d].[TableName] = 'TrainerClass' AND 
        [d].[Language] = 'en-EN';

score:1

If I'm reading your request right, you'll need to join the table twice with different filters.

select t.*, dName.Description as [Name], dInfo.Description as [Info]
from TrainerClass t
join DescriptionTranslation dName
    on t.Code = dName.Code and dName.FieldName = 'Name'
join DescriptionTranslation dInfo
    on t.Code = dInfo.Code and dInfo.FieldName = 'Info'
where
    dName.TableName = 'TrainerClass' and dName.Language = 'en-EN'
    and dInfo.TableName = 'TrainerClass' and dInfo.Language = 'en-EN'

score:1

Another way you can use subqueries for your query and it's quite similar to your intention. But only one caveat with this method, that is it can be quite slow if you have large record-sets.


SELECT [t].*,
( SELECT [d].[Description]  FROM [DescriptionTranslation] [d] WHERE [d].[FieldName] = 'Name' and [t].[Code] = [d].[Code] [d].[TableName] = 'TrainerClass' AND 
[d].[Language] = 'en-EN') AS [Name], 
(SELECT [d].[Description]  FROM [DescriptionTranslation] [d] WHERE [d].[FieldName] = 'Info' and [t].[Code] = [d].[Code] [d].[TableName] = 'TrainerClass' AND 
[d].[Language] = 'en-EN') AS [Info]     
FROM   [TrainerClass] AS [t] 

score:4

You can use a CASE statement like below (I think that is what you are looking for). Mind you, mismatching rows in each case will get null values or you can add the else part to the case statement.

SELECT [t].*,
       CASE WHEN [d].[FieldName] = 'Name' THEN [d].[Description] END AS [Name],
       CASE WHEN [d].[FieldName] = 'Info' THEN [d].[Description] END AS [Info]

FROM   [TrainerClass] AS [t] join [DescriptionTranslation] AS [d] 
                             on [t].[Code] = [d].[Code]
WHERE  [d].[TableName] = 'TrainerClass' AND  --[d].[FieldName] = 'Name' AND 
       [d].[Language] = 'en-EN';

UPDATE:

As per your update, to get expected results from shown picture table, you can use Max() function with Group by:

;With cte as 
(
    --your first query goes here 
)
Select Id,Code, Max(Name) Name, Max(info) Info
from cte
Group by Id, Code

Of you can do without CTE as:

SELECT t.Id, t.Code,
       Max(CASE WHEN d.FieldName = 'Name' THEN d.Description END) AS Name,
       Max(CASE WHEN d.FieldName = 'Info' THEN d.Description END) AS Info
FROM   [TrainerClass] AS t join [DescriptionTranslation] AS d  on t.Code = d.Code
WHERE  d.TableName = 'TrainerClass' AND  --[d].[FieldName] = 'Name' AND 
       d.Language = 'en-EN'
GROUP BY t.Id, t.Code;

More questions

More questions with similar tag