here's the full illustration for your exampledeclare @tableA table([ID] int,[COL1] int,[COL2] int,[COL3] int)insert @tableAvalues(1, 100, 110, 150),(2, 90, 100, 120),(3, 75, 85, 110)declare @tableB table([ID] int,[COLUMNS] varchar(10), [CO] decimal(5,1), [EQ] int)insert @tableBvalues(1, 'COL1', 0.2, 100),(2, 'COL2', 0.5, 110),(3, 'COL3', 0, 90);With CTE1AS(SELECT *FROM @tableA tUNPIVOT( [Val] FOR [COLUMNS] IN ([COL1],[COL2],[COL3]))u),CTE2AS(SELECT ID,CAST(Val AS float) AS Val,COLUMNSFROM CTE1 c1WHERE NOT EXISTS(SELECT 1 FROM CTE1 WHERE ID < c1.ID)UNION ALLSELECT c1.ID,CAST(c1.Val + (c2.Val * b.CO) AS float),c1.COLUMNSFROM CTE2 c2INNER JOIN CTE1 c1ON c1.ID = c2.ID + 1 AND c1.COLUMNS=c2.COLUMNSINNER JOIN @tableB bON b.COLUMNS=c2.COLUMNS)SELECT *FROM CTE2PIVOT(SUM(Val) FOR COLUMNS IN ([COL1],[COL2],[COL3]))poutput----------------------------------------------ID COL1 COL2 COL31 100 110 1502 110 155 1203 97 162.5 110
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/