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?
-- 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:
- 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