T-sql – Get Row Value by Column Name of Name at runtime

t-sql

I currently have a stored procedure that obtains rows from an import table and joins another table which has a row for each column header of the import table.

I am able to query this but in order to obtain the value I need to utilize a huge case statement.

Is there anyway to dynamically get a column value from a row by simply knowing the nvarchar type of the column name?

Here is my current procedure:

...
SELECT

import.ImportID,
f.FieldID,
f.FieldName,

CASE

    WHEN f.FieldName = 'Test'
    THEN import.Test
    WHEN f.FieldName = 'Test2'
    THEN import.Test2
    ELSE
        0

END AS [Value]
--import.[f.FieldName] AS [Value]

FROM [Import_Table] import
LEFT JOIN #Fields f ON (1 = 1)

END

The issue I have is that there are 40+ columns in the import table, case statements aren't too bad, however, would be difficult to maintain/record moving forward. Is there another way to obtain the data dynamically?

Perhaps a temporary pivot table and joining on column names?

TIA

— edit —

-- this is just used from another table
CREATE TABLE #Fields (FieldID bigint, FieldName VARCHAR(50))

-- this is import table
TABLE Import_Table (ImportID bigint, UserID bigint, Test decimal(5,2), Test2 decimal(5,2), Test3 decimal(7,3), ....

Fields is populated as:

  1. Test
  2. Test2
  3. Test3
  4. Test4
  5. Test5
  6. Test6
    …

— edit 2

//Import Table
1,323,2.3,4.5,3.0,8.803,...

//expected output
1,1,Test1,2.3
1,2,Test2,4.5
1,3,Test3,3.0
1,4,Test4,8.803

Best Answer

You can use the UNPIVOT function to achieve this result set without having to join to the Field table at all.

This query shows how this works (only showing 3 columns for brevity):

SELECT Import_id, 
  p.Field, 
  p.Value
FROM
(
  SELECT Import_id,
    CAST(Test1 AS VARCHAR) AS Test1, 
    CAST(Test2 AS VARCHAR) AS Test2, 
    CAST(Test3 AS VARCHAR) AS Test3
  FROM Import_Table
) src
UNPIVOT
(
  Value FOR Field IN (Test1, Test2, Test3)
) p

You can see this in action here in the 3rd query in that db<>fiddle. For more columns, you simply add them to the inner SELECT statement and to the IN clause of the UNPIVOT statement. Alternatively, you can leverage the Fields table and dynamic SQL to build your UNPIVOT statement like the 4th query in the db<>fiddle.

This example requires SQL 2016 or higher as it uses the STRING_AGG() function, earlier versions will work with this method but require a custom string aggregation function.

DECLARE @Sql NVARCHAR(MAX)

SET @Sql = '
SELECT Import_id, 
  p.Field, 
  p.Value
FROM
(
  SELECT Import_id,
    '

SELECT @Sql = @Sql + 
  STRING_AGG('CAST(' + FieldName + ' AS VARCHAR) AS ' + FieldName, ', ')
FROM Fields

SET @Sql = @Sql + '
  FROM Import_Table
) src
UNPIVOT
(
  Value FOR Field IN ('

SELECT @Sql = @Sql + 
  STRING_AGG(FieldName, ', ')
FROM Fields
SET @Sql = @Sql + ')
) p'


EXEC sp_executesql @Sql
Related Question