sql server - T-SQL Query - Help Required -

i have 2 tables , b;

table a: cust_id, col_a, col_b 1001, 1, 2 1002, 3, 4 1003, 2, 1 1004, 4, 3  table b: col_a_weight, col_b_weight 0.1111111, 0.22222 

what i'm trying create new table c using t-sql,

output = col_a*col_a_weight + col_b*col_b_weight 

table c:

cust_id, output 1001, 1*0.1111111 + 2*0.22222  1002, 3*0.1111111 + 4*0.22222 1003, 2*0.1111111 + 1*0.22222 1004, 4*0.1111111 + 3*0.22222 

so final table c is

cust_id output 1001, 0.5555511 1002, 1.2222133 1003, 0.4444422 1004, 1.1111044 

given there 200 col_a, col_b, col_c, col_d etc , corresponding weight columns, how use clever join columns referenced dynamically (given correspond across both tables). since table b has no cust_id field, makes little more trickier.

how can done?

use tempdb; go  create table dbo.a (     cust_id int primary key,     col_a int,     col_b int,     col_c int,     col_d int,     col_e int );  insert dbo.a     select 1001, 1, 2, 2, 2, 2     union select 1002, 3, 4, 3, 3, 3     union select 1003, 2, 1, 5, 2, 1     union select 1004, 4, 3, 2, 3, 4;  create table dbo.b (     col_a_weight decimal(10,9),     col_b_weight decimal(10,9),     col_c_weight decimal(10,9),     col_d_weight decimal(10,9),     col_e_weight decimal(10,9) );  insert dbo.b     select 0.1111111, 0.22222, 0.3333, 0.444, 0.55;  declare @sql nvarchar(max) = n'if object_id(''dbo.c'') not null begin     drop table dbo.c; end select a.cust_id, output = ';  select @sql += 'b.' + name + '*'      + replace(name, '_weight', '') + ' + ' sys.columns  [object_id] = object_id('dbo.b');  select @sql = left(@sql, len(@sql)-2)     + ' dbo.c         dbo.a         cross join dbo.b b;';  exec sp_executesql @sql;  select * dbo.c;  go drop table dbo.a, dbo.b;