I want to write a query using join in which dynamic column name and value will come from different table in SQL server

See more:

SQL

Hi,

I want a query according case when Table2.Col2=1 then select X column name from table3 inside col3 of table 1 and we also have condition to select column name from table3,table4,table5.....

Please suggest . thanks for you support.

Table1			
Col1	Col2	Col3
1	     12	
2	     20	
3	     30	

Table2		
Col1	Col2	Col3
1	     X	    Table3
2	     Y 	    Table4
3	     Z	    Table4

What I have tried:

I have tried this query but it return only col2 name from table2 but i need select X column from table3

Select MC.Min,MC.Max,
MC.DataType,
MC.ScriptFieldId,
t2.Col1,
CASE WHEN Isnull(MC.ScriptFieldId,0) <> 0 THEN
CASE WHEN t2.Col1 ='1' THEN (SELECT t2.Col2 FROM Table3 CTP where TravellerId =@TravelerId) -- select X column from table3
WHEN t2.Col1 ='2' THEN (SELECT t2.Col2 FROM Table4 CCP1 where CCP1.CompnayId =CCP.CompnayId) -- select Y column from table4
ELSE SFM.ColumnName END ELSE '' END AS 'MappingValue'
FROM ManageScript MC
LEFT OUTER JOIN Table2 t2 on t2.Col1 =MC.Col1
WHERE MC.ClientId =@ClientId and MC.CompanyId =@CompanyId