Please let me know the query to update this

Question

Table Name : Hist_table

 Shp_cd wt_grp  net_wt  tot_wt
   101   10       9      7
   102   20       8      2
   103   15       4      1

Factor_table

Fact_id fact_column factor
  1     wt_grp        2
  2     net_wt        5
  3     tot_wt        3

Note – this factor table contains rows as the column name of hist_table.
Now we have to update the Hist_table with the multiplied with the factors
For example wt_grp factor is 2 then we have to update all the wt_grp column of hist table as Hist_table.wt_grp * factor_table.factor
So the result should be

shpcd   wt_grp  net_wt  tot_wt
101     20        45    21
102     40        40    6
103     30        20    3

Answer 1

-- sample data

DECLARE @Hist TABLE (Shp_cd int, wt_grp int, net_wt int, tot_wt int)
INSERT INTO @Hist (Shp_cd, wt_grp, net_wt, tot_wt) VALUES (101, 10, 9, 7)
INSERT INTO @Hist (Shp_cd, wt_grp, net_wt, tot_wt) VALUES (102, 20, 8, 2)
INSERT INTO @Hist (Shp_cd, wt_grp, net_wt, tot_wt) VALUES (103, 15, 4, 1)

DECLARE @Factor TABLE (Fact_ID INT IDENTITY(1,1), fact_column varchar(6), factor int)
INSERT INTO @Factor (fact_column, factor) VALUES ('wt_grp', 2)
INSERT INTO @Factor (fact_column, factor) VALUES ('net_wt', 5)
INSERT INTO @Factor (fact_column, factor) VALUES ('tot_wt', 3)

-- cross join to pick up factors

SELECT 
    h.shp_cd,
    MAX(CASE WHEN f.fact_column = 'wt_grp' THEN wt_grp * factor END) AS wt_grp,
    MAX(CASE WHEN f.fact_column = 'net_wt' THEN net_wt * factor END) AS net_wt,
    MAX(CASE WHEN f.fact_column = 'tot_wt' THEN tot_wt * factor END) AS tot_wt
FROM 
    @Hist h
     CROSS JOIN 
    @Factor f
GROUP BY h.Shp_cd -- to bring it down to a single resultset