Iterating through multiple columns in SQL Server 2008

Data and Databases
1

I’m working with a database table that has multiple similar columns and I need to transform the data into a normalized format.

Current table structure (TableX):

TableX
    RecordID  cost1  cost2  cost3  cost4  factor1  factor2  factor3  factor4
    100       25     30     35     40     1.5      2.1      1.8      2.3

Target table structure (TableY):

RecordID  Cost  Factor
100       25    1.5
100       30    2.1
100       35    1.8
100       40    2.3

Currently I’m using a cursor to retrieve data:

Fetch next from my_cursor into @record_id, @cost1, @cost2, @cost3, @cost4, @factor1, @factor2, @factor3, @factor4

Then I need to insert the data with statements like:

insert into TableY (RecordID, Cost, Factor) values (@record_id, @cost1, @factor1)

The problem is I would need to write multiple insert statements for each column pair. Since I actually have about 30 similar columns in my real scenario, writing individual insert statements becomes impractical.

Is there an efficient way to iterate through these column pairs so I can use a single insert operation within a loop structure instead of writing 30 separate insert statements?

2

unpivot is your friend here! it turns your cols into rows super easy. try this: SELECT RecordID, Cost, Factor FROM (your base query) UNPIVOT (Cost FOR CostType IN (cost1,cost2,cost3,cost4)) unpvt1 UNPIVOT (Factor FOR FactorType IN (factor1,factor2,factor3,factor4)) unpvt2 - just ensure pairs are correct!

3

wow, 30 cols is def a challenge! have you considered dynamic SQL? u could build the insert stmt in a loop & run it all at once. what’s ur column naming pattern? if they follow a sequence like cost1, cost2, it’ll be easier to generate the query!

4

Use CROSS APPLY with a table value constructor. No cursors or multiple inserts needed, and the code stays clean.

INSERT INTO TableY (RecordID, Cost, Factor)
SELECT RecordID, Cost, Factor
FROM TableX
CROSS APPLY (
    VALUES 
    (cost1, factor1),
    (cost2, factor2),
    (cost3, factor3),
    (cost4, factor4)
) AS t(Cost, Factor)

This handles everything in one operation - no dynamic SQL required. For your 30 columns, just add more pairs to the VALUES clause. CROSS APPLY creates a Cartesian product between each TableX row and your value pairs, which unpivots the data. It’s faster than cursors and lets SQL Server optimize properly.