SQL and Joining in SQL Server 2005

Suppose I have two tables in SQL Server one is Emp and another is FieldsInfo开发者_开发技巧.

Emp table looks with data like below

FieldsInfo table looks with data like below

Now I want to join both the tables in such a way that SQL Server would show data from Emp table but field name will be shown from FieldsInfo table according to the relation.

I mean Employee ID will be shown as Field name instead of ID and Salary will be shown as field name instead of Sal but value will be just like Emp table.

I am not able to generate this type of output after joining. So please help with right SQL script.

thanks

You can use your FieldsInfo table to build the SQL statement dynamically.

declare @SQL nvarchar(100)
set @SQL = ''

select @SQL = @SQL + FieldName + ' as [' + Description + '], '
from FieldsInfo
where TableName = 'Emp'

set @SQL = 'select '+ left(@SQL, len(@SQL)-1) + ' from Emp'

exec sp_executesql @SQL

declare @SQL nvarchar(100)
set @SQL = ''
select @SQL =  stuff((select ', ' + [FieldName] + ' as ['+ [Description] +']'
                  from FieldsInfo t2 
                  where t2.TableName = t1.TableName 
                  for xml path('')),1,1,'') 
    from FieldsInfo t1
    group by TableName   

set @SQL = 'select '+ @SQL + ' from Emp'

exec sp_executesql @SQL

From how I understand your question what you're trying to achieve is not possible because you needed a dynamic AS clause which is unsupported.

Example:

SELECT EmpName AS *(SELECT TOP 1 Description FROM FieldsInfo WHERE FieldName = 'EmpName')*
FROM Emp

I guess you should try to do the mapping in whatever application the results are actually used. If you need the output in SQL Server I'm not sure if temp tables will do the trick.

I wouldn't try to use a table for field names, as you would be adding an extra layer of complexity.

SELECT 
    ID [Employee ID], 
    EmpName [Employee Name], 
    Sal [Salary] 
FROM 
    Emp