Sql-server – Mapping Fields based on unknown relationship

sql server

I have a challenging SQL query to work out (SQL Server).
I have one table that will show mappings between the two tables.
So below, field F1 is a scanner id

enter image description here

and table two, has the scanner Id in F1. F2 has the Location, and so on.

enter image description here

The top table can be updated to change mappings at any time.
So my question is how do I create a query so I know what field name relates to what data in table two.

I started off with an UNPIVOT, but didn't get too far as yet. If I could get the field name in the unpivot results then I am a long way to solving it.

ID,[F1] ,[F2]  ,[F3]  ,[F4]  ,[F5]  ,[F6]  ,[F7]  ,[F8]  ,[F9] ,[F10]
FROM [Inventory].[dbo].[ScannedDataCapture]

) AS cp
Data FOR Datas IN ([F1] ,[F2]  ,[F3]  ,[F4]  ,[F5]  ,[F6]  ,[F7]  ,[F8]  ,[F9] ,[F10])
) AS x

Thanks upfront. Scott

Best Answer

You can use FOR XML PATH to help construct a dynamic query:

-- Generate a list of field names to be used in the SELECT statement
    SELECT '[' + FieldNumber + '] AS [' + FieldName + '],'
    FROM Datas
    ORDER BY FieldName DESC -- Sample column ordering
    FOR XML PATH('')

-- Generate a SELECT query; make sure to remove the comma after the last field
SET @Sql = 'SELECT ' + SUBSTRING(@Sql,1, LEN(@Sql) - 1) + ' FROM dbo.ScannedDataCapture'

-- Show SQL before execution
EXEC sp_sqlexec @Sql

Screenshot of dynamic query output