1

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.

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

2
  • Did you try using dynamic SQL?
    – BuahahaXD
    Mar 24 '16 at 13:56
  • Its a consideration certainly. I may try also to hit it as the C# end (source input) Cheers Mar 24 '16 at 14:09
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

Screenshot of dynamic query output

1
  • that will worked perfect. Thanks for your advice. Scott Mar 27 '16 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.