Dynamic "column" in select with cast

Question

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

My tables are as follows;

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

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]

Both "StringVal" and "IntVal" will be set based on the "DataType" column in the "Fields" table, if set to "lookup" (which is stored as a string in the "Data" table), it should be parsed as an integer within this select query and populated within the "IntVal" column, otherwise, it should be parsed directly to the [StringVal] column.

I've got the following SQL query, which works as intended, however, if I have 200 data fields, I'd have to replicate the CASE and WHEN clauses 200 times per string and lookup, and then there is the complexity of adding a new type such as "DateTime", which will cause further issues.

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

Is there an easier way of achieving this? I need to keep this within an indexed view, therefore cannot use stored procedures, functions, etc.

EDIT:

Attempted to achieve this via functions as stated in the comments below, however found the same issue. Below is my attempted function solution.

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

Functions:

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

Is there any way to embed a "row" into a function? Ideally I want to submit 2 parameters, the field number and the "dt" result set.

EDIT 2:

Found a way to make a function take a type... which works... but can't figure out how to parse "dt.*" from the query into that type and pass through to the function.

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

Dynamic "column" in select with cast

1 Answers