Mapping Fields based on unknown relationship

Question

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

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

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.

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

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

Thanks upfront. Scott

Answer 1

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
DECLARE @Sql VARCHAR(MAX) =
(
    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
PRINT @Sql
EXEC sp_sqlexec @Sql