0

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
  • Pivot seems like a viable option. Can you server up a DDL and DML statement for this? (create table....) and (insert into table... values... ) – scsimon Oct 15 '19 at 19:53
  • @scsimon I've updated the post. Would you have a link/example to how to properly use pivot in this use case? – Jake Dev Oct 15 '19 at 19:58
  • More info is needed. Provide some example data for both tables and expected output. – HandyD Oct 16 '19 at 4:58
  • @HandyD i've updated the answer, what more information do you need? – Jake Dev Oct 16 '19 at 13:53
0

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

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.