Dynamic "column" in the choice of cast

Question

I am looking for a way to retrieve data in a table based on a condition from another SQL query.

My tables are as follows:

DataTable: Id | EntityId | Value1 | Value2 | Value 3 ... Value 200

Entity: Id | Name

Fields: Id | EntityId | DataType | Fieldnum

Lookups: Id | EntityId | Fieldid

My ideal output is as follows:

• Data.Id [Id]
• Field.Id [FieldId]
• Entity.Id [EntityId]
• [StringVal]
• [IntVal]

"StringVal" "IntVal" "DataType" "", "lookup" ( "" ), "IntVal", [StringVal].

SQL-, , , 200 , CASE WHEN 200 , , "DateTime", .

SELECT
  dt.Id as [Id],
  f.Id as [FieldId],
  f.EntityId as [EntityId],
  CASE
    WHEN f.DataType = 'string' and f.FieldNum = 0 THEN dt.Value0
    WHEN f.DataType = 'string' and f.FieldNum = 1 THEN dt.Value1
    WHEN f.DataType = 'string' and f.FieldNum = 2 THEN dt.Value2
    WHEN f.DataType = 'string' and f.FieldNum = 3 THEN dt.Value3
    WHEN f.DataType = 'string' and f.FieldNum = 4 THEN dt.Value4
    WHEN f.DataType = 'string' and f.FieldNum = 5 THEN dt.Value5
    WHEN f.DataType = 'string' and f.FieldNum = 6 THEN dt.Value6
    WHEN f.DataType = 'string' and f.FieldNum = 7 THEN dt.Value7
    WHEN f.DataType = 'string' and f.FieldNum = 8 THEN dt.Value8
    WHEN f.DataType = 'string' and f.FieldNum = 9 THEN dt.Value9
    ELSE NULL
  END as [StringVal],
  CAST(CASE
    WHEN f.DataType = 'lookup' and f.FieldNum = 0 THEN dt.Value0
    WHEN f.DataType = 'lookup' and f.FieldNum = 1 THEN dt.Value1
    WHEN f.DataType = 'lookup' and f.FieldNum = 2 THEN dt.Value2
    WHEN f.DataType = 'lookup' and f.FieldNum = 3 THEN dt.Value3
    WHEN f.DataType = 'lookup' and f.FieldNum = 4 THEN dt.Value4
    WHEN f.DataType = 'lookup' and f.FieldNum = 5 THEN dt.Value5
    WHEN f.DataType = 'lookup' and f.FieldNum = 6 THEN dt.Value6
    WHEN f.DataType = 'lookup' and f.FieldNum = 7 THEN dt.Value7
    WHEN f.DataType = 'lookup' and f.FieldNum = 8 THEN dt.Value8
    WHEN f.DataType = 'lookup' and f.FieldNum = 9 THEN dt.Value9
    ELSE NULL
  END as INT) as [IntVal]
FROM Lookups as i
  left JOIN Fields f on i.FieldId = f.id and i.EntityId = f.EntityId
  INNER join DataTable dt on i.EntityId = dt.EntityId

? , , ..

EDIT:

, , . .

SELECT
  dt.Id as [Id],
  f.Id as [FieldId],
  f.EntityId as [EntityId],
  CASE
    WHEN f.DataType = 'string' THEN dbo.u_function_return_string
      (
        f.FieldNum, dt.Value0, dt.Value1,
        dt.Value2, dt.Value3, dt.Value4,
        dt.Value5, dt.Value6, dt.Value7,
        dt.Value8, dt.Value9
      )
    ELSE NULL
  END as [StringVal],
  CAST(CASE
    WHEN f.DataType = 'lookup' THEN dbo.u_function_returns_string
      (
         f.FieldNum, dt.Value0, dt.Value1,
         dt.Value2, dt.Value3, dt.Value4,
         dt.Value5, dt.Value6, dt.Value7,
         dt.Value8, dt.Value9
      )
    ELSE NULL
  END as INT) as [IntVal]
FROM dbo.Lookups as i
  INNER JOIN dbo.Fields f on i.FieldId = f.id and i.EntityId = f.EntityId
  INNER join dbo.DataTable dt on i.EntityId = dt.EntityId

:

CREATE FUNCTION u_function_return_string(
  @fieldNum INT,
  @val0 NVARCHAR(255),
  @val1 NVARCHAR(255),
  @val2 NVARCHAR(255),
  @val3 NVARCHAR(255),
  @val4 NVARCHAR(255),
  @val5 NVARCHAR(255),
  @val6 NVARCHAR(255),
  @val7 NVARCHAR(255),
  @val8 NVARCHAR(255),
  @val9 NVARCHAR(255)
)
RETURNS NVARCHAR(255) WITH SCHEMABINDING
AS BEGIN
RETURN
  CASE
    WHEN @fieldNum = 0 THEN @val0
    WHEN @fieldNum = 1 THEN @val1
    WHEN @fieldNum = 2 THEN @val2
    WHEN @fieldNum = 3 THEN @val3
    WHEN @fieldNum = 4 THEN @val4
    WHEN @fieldNum = 5 THEN @val5
    WHEN @fieldNum = 6 THEN @val6
    WHEN @fieldNum = 7 THEN @val7
    WHEN @fieldNum = 8 THEN @val8
    WHEN @fieldNum = 9 THEN @val9
  END
END
GO

"" ? 2 , "dt".

2:

... ... , "dt. *" .

CREATE OR ALTER FUNCTION u_function_return_string(
  @fieldNum INT,
  @data dbo.DataTableType READONLY
)
RETURNS NVARCHAR(255) WITH SCHEMABINDING
AS BEGIN
  DECLARE @tmp NVARCHAR(255)
  DECLARE @value NVARCHAR(255)
  set @tmp = 'Value' + CAST(@fieldNum as NVARCHAR(50))
  SET @value = (SELECT @tmp FROM @data)
  RETURN @value
END
GO

Answer 1

sql case select.

, . .

  SELECT
  dt.Id as [Id],
  f.Id as [FieldId],
  f.EntityId as [EntityId],
  CASE
    WHEN f.DataType = 'string' THEN 
         dbo.fn_getvalue(f.FieldNum,f.EntityId)
    ELSE NULL
  END as [StringVal],
  CAST(CASE
    WHEN f.DataType = 'lookup' THEN 
         dbo.fn_getvalue(f.FieldNum,f.EntityId)
    ELSE NULL
  END as INT) as [IntVal]
FROM Lookups as i
  left JOIN Fields f on i.FieldId = f.id and i.EntityId = f.EntityId
  INNER join DataTable dt on i.EntityId = dt.EntityId 




   CREATE FUNCTION [dbo].[fn_getvalue]
    (@param1 int, @param2 int)
    RETURNS nvarchar(255) WITH SCHEMABINDING
    AS
    BEGIN
        declare @sql nvarchar(max); 
        declare @output nvarchar(50);

        set @sql = 'SELECT @field=Value' + @param1  + ' FROM DataTable WHERE EntityId = ' + cast(@param2 as nvarchar);

        execute sp_executesql @sql,N'@field nvarchar(50) OUTPUT', @field = @output OUTPUT;
        return @output;
    END