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
- SQL WHERE statements in SELECT statements
- How to apply WHERE clause to multiple SELECT statements in SQL Server
- SQL Server SELECT where any column contains 'x'
- SQL SELECT WHERE string ends with Column
- SQL Server 2008 - Case / If statements in SELECT Clause
- SQL Server SELECT statements causing blocking
- SQL Server DELETE and SELECT Behave Differently with Same WHERE Clause
- Select Count Where Values greater than 0 SQL Server
- SQL group by: select value where another column has its min/max
- SQL Server select where datetimeoffset older than 1 hour
- SQL Server 2016 select where in json array
- SQL Server - use Exists clause in Where and Select
- Stop LINQ to SQL from executing select statements after insert
- SQL Select Statement Where
- SQL Server SELECT Where Value Equals Results of Another Select
- SQL select from inner join where count greater than
- C# with SQL Server SELECT WHERE IN with data list
- SQL select where column begins with Letters
- SQL Server: Combine several SELECT statements with "WITH" part into a UNION
- SQL Server select variable where no results
- SQL SELECT WHERE column has all delimited strings
- SQL Server - Select rows where one column matches but the other does not
- Can i use column number instead of column name in where condition of a select query in SQL Server 2005?
- SQL Server: Select Where String Doesn't Contain Certain Value
- SQL select where startdate is today's date
- SQL Server : select from parent where id doesnt exist in either child tables
- How do I combine SELECT statements to allow me to calculate percentages, successes and failures in SQL Server?
- Conditionally appending AND to where clause in SQL Select statement
- SQL Server 2008 : how to select sum of all sessions where difference between two consecutive sessions is less than 10 minutes
- SQL Server XML XQuery select where clause attribute
More questions with similar tag
- Performance issue with correlated SQL Server query
- Find any duplicate value in different columns SQL Server
- SQL: How use case and cast in a query?
- Working with Time portion of a DateTime Column in SQL Server2008
- Sql Server 2005 SSIS/Agent - Query status of a job
- Establishing connection to Azure SQL server. "Error: Token retrieval failed with an error. Open developer tools to view the error"?
- "Cannot create an instance of OLE DB provider" error as Windows Authentication user
- How to update table and add column value in SQL Server?
- Python: Execute Stored Procedure with Parameters
- How to execute stored procedure in SQL Server from C# chatbot with bot application?
- Add "DateModified" Column with an Update Trigger To All Tables that contain a "DateCreated" Column
- Calculating Values in SQL Query and using those values in other calculations in the same query
- Inserting NULL integer using VB.Net and EF5
- SQL Server VDI Backup
- How to use FOR XML in Subqueries?
- TSQL: Table type not being handled as temp table?
- How can I get a list of all of the user databases via t-sql?
- SQL Server Tedious Callback
- Boolean expression as column value in transact sql
- SQL Select only records with no duplicates
- How To Select Distinct Row Based On Multiple Fields
- SQL Server : getting certain table names from query
- SQL Server: copy a row and insert into a new row with a different ID
- What is the purpose of setting SQL TRUSTWORTHY ON
- How to combine the result of SQL server table rows using bitwise operator?
- BULK INSERT fails with last row
- time range and date range in TSQL
- Joining 3 tables on 2 columns?
- SQL Proc 'Conversion failed' from varchar to int. Why the conversion?
- How to connect to SQL Server from a client machine?