sql - Joining data from three tables to populate another table -

i have following table structures in database

dbo.customer:

customer_pkcustomer_name
1                   acustomer        
2                   bcustomer        
3                   ccustomer        

dbo.import

                    aproduct      bproduct        cproduct    

acustomer  sale value1 sale value2    salevalue3

bcustomer  sale value4 sale value5    salevalue6

ccustomer  sale value7 sale value8    salevalue9

dbo.product:

product_pk   product_name
1                   aproduct        
2                   bproduct        
3                   cproduct        

which need join populate table dbo.cost (the reason have separate cost table because same product may have different costs different customers)

dbo.cost:

cost_pk cost_valuecustomer_fkproduct_fk
cell 1      cell 2          cell 3             cell 4          
cell 5      cell 6          cell 7             cell 8          
cell 9      cell 10        cell 11           cell 12        

so table dbo.cost needs describe customers own products , how paid.

edit: sql create scripts:

create table [dbo].[customer]( [customer_pk] [int] identity(1,1) not null, [customer_name] [varchar](100) not null, constraint [pk_customer] primary key clustered  ( [customer_pk] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off,  allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]  go  create table [dbo].[product](     [product_pk] [int] identity(1,1) not null,     [product_name] [varchar](100) not null,  constraint [pk_product] primary key clustered  (     [product_pk] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]  go  create table [dbo].[cost](     [cost_pk] [int] identity(1,1) not null,     [cost_value] [varchar](10) not null,     [customer_fk] [int] not null,     [product_fk] [int] not null,  constraint [pk_cost] primary key clustered  (     [cost_pk] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]  go  set ansi_padding off go  alter table [dbo].[cost]  check add  constraint [fk__cost__product_fk__5165187f] foreign key([product_fk]) references [dbo].[product] ([product_pk]) go  alter table [dbo].[cost] check constraint [fk__cost__product_fk__5165187f] go  alter table [dbo].[cost]  check add  constraint [fk__cost__customer_fk__5070f446] foreign key([customer_fk]) references [dbo].[customer] ([customer_pk]) go  alter table [dbo].[cost] check constraint [fk__cost__customer_fk__5070f446] go 

assuming want go costtable value intersecting product column , customer rows in importtable , first column in importtable named customer_name, think unpivot importtable.

if number of products fixed query it:

select cost_value, customer.customer_pk, product.product_pk     (select customer_name, aproduct, bproduct, cproduct    import) p unpivot    (cost_value product_name in        (aproduct, bproduct, cproduct) )as unpvt inner join product on unpvt.product_name=product.product_name inner join customer on unpvt.customer_name=customer.customer_name; 

but guess need dynamic solution (adapting varying number of product columns in import table) , if (and if assumptions made above hold) following query should work:

declare @cols nvarchar(max) select @cols =  stuff((select ',' + quotename(product_name)                 (                     select c.name product_name sys.columns c                     inner join sys.tables t on c.object_id = t.object_id                      t.name = 'import' , c.name != 'customer_name') x                  order product_name xml path(''), type).value('.', 'nvarchar(max)') ,1,1,'')  declare @query nvarchar(max) set @query = '  --insert cost (cost_value,customer_fk,product_fk)                  select cost_value, c.customer_pk, p.product_pk                                      (select customer_name, ' + @cols + ' import) p                 unpivot                     (cost_value product_name in (' + @cols + ')                     ) unpvt                 inner join product p on unpvt.product_name = p.product_name                 inner join customer c on unpvt.customer_name = c.customer_name;'   exec sp_executesql @query 

given sample data in question query produce following output (that inserted cost - i've commented out insert in query):

cost_value           customer_pk product_pk -------------------- ----------- ----------- sale value1          1           1 sale value2          1           2 sale value3          1           3 sale value4          2           1 sale value5          2           2 sale value6          2           3 sale value7          3           1 sale value8          3           2 sale value9          3           3