How to SELECT a column based on a condition on the column's name? I have the following table named MyTable:

+------+--------+--------+--------+
| Code | U_Col1 | U_Col2 | U_Col3 |
+------+--------+--------+--------+
|  123 |      1 |      2 |      3 |
|  456 |      4 |      5 |      6 |
+------+--------+--------+--------+


I want to write a query to give me the output based on the condition on the column's name. The names of the column are not fixed. They can be anything and come from another table.

CREATE FUNCTION [dbo].[udf_Value] (@Code VARCHAR(3), @Name VARCHAR(10))
RETURNS TABLE AS RETURN (

    SELECT *
    FROM MyTable T0
    WHERE T0.Code = @Code AND &lt;columnname&gt; = CONCAT('U_',@Name)
)


The expected output of 

SELECT * FROM [dbo].[udf_Value]('123','Col3') 


is 3.

How do I get this kind of a WHERE condition to work? I realize I would normally do it with dynamic SQL, but I want to do this in an inline table-valued function, which won't allow me doing it dynamically.

I tried an UNPIVOT:

SELECT X.Value
FROM (SELECT * FROM MyTable WHERE Code = @Code) T
UNPIVOT (
Value FOR Name IN (@Name)
) AS X


But there is something wrong with the syntax.
You can do this without dynamic SQL with some XML trickery.

Create XML of all values for each row and and use local-name() function to extract the value of the column you want.

Here is an example where you perhaps need to change the data types. I assumed integers.

create table dbo.T(Code int, U_Col1 int, U_Col2 int, U_Col3 int)

go

create function dbo.GetT
(
  @Name nvarchar(128),
  @Code int
)
returns table
as
return (
       select T.Code,
              C.X.value('(*[local-name() = sql:variable("@Name")]/text())[1]', 'int') as U_Col
       from dbo.T
         cross apply(select T.* for xml path(''), type) as C(X)
       where T.Code = @Code
       );
go

insert into dbo.T (Code, U_Col1, U_Col2, U_Col3) values
(123, 1, 2, 3),
(456, 4, 5, 6)

go

select * from dbo.GetT('U_Col1', 123);
select * from dbo.GetT('U_Col2', 456);


Note that the names of all columns in the table must be valid as element names in XML.

dbfiddle.uk