MS-SQL getting case specific values

Question

We have a table LogicalTableSharing as follows:

For a specific requirement, we need to take PhysicalCompany of each TableCode into a variable. We tried a case-based query as follows:

declare @tablecode varchar(50)
declare @inputcompany varchar(50)

declare @query nvarchar(2500)


set @inputcompany= 91


set @query = '
select '''+@inputcompany+''' AS inputcompany,
       CASE WHEN lts.TableCode = ''tsctm005'' THEN lts.PhysicalCompany ELSE NULL END as tsctm005_company,
       CASE WHEN lts.TableCode = ''tccom000'' THEN lts.PhysicalCompany ELSE NULL END as tccom000_company
from LogicalTableSharing lts
where  lts.LogicalCompany = '''+@inputcompany+'''
'
EXEC sp_executesql @query

which obviously gives the result as

The desired output is

What is the right approach?

Answer 1

Try subqueries in a FROM-less SELECT. For performance you want an index on (logicalcompany, tablecode) (or the other way round depending on which is more selective).

SELECT @inputcompany inputcompany,
       (SELECT TOP 1
               physicalcompany
               WHERE logicalcompany = @inputcompany
                     AND tablecode = 'tsctm005'
               ORDER BY <some criteria>) tsctm005_company,
       (SELECT TOP 1
               physicalcompany
               WHERE logicalcompany = @inputcompany
                     AND tablecode = 'tccom000'
               ORDER BY <some criteria>) tccom000_company;

You should find <some criteria> to order by in case of multiple possible rows to decide which one takes precedence. Unless you just want a random one possibly each time you run the query another one, that is.