Joining Table to populate the third table

Question

I have 2 tables

Product        FiledName       Value
AA               color           red
AA               price           10.2
AA               ID              X1   
BB               Color           Blue
BB               price           20.2 
BB               ID              X2

second table

ID   Code
X1    123A
X2    123B

I need to populate the third table with the following information from the previous tables

Code    FiledName       Value
123A     color           red
123A     price           10.2
123B     color           red
123B     price           20.2

What's the easiest way to do this?

Answer 1

So here is what I did to give us the following results, It slightly varies from your third table value however, this is more normalized and would be easier to manipulate from here.

product id  color   price
AA      X1  red     10.2
BB      X2  Blue    20.2

This allows us to get these results

select pvt.product,pvt.id,color,price from one PIVOT
(
    MAX(value)
    FOR filedname IN (color,price,id)
)AS pvt
inner join two on two.id=pvt.id

Staging --

create table one
( 
product varchar (20),
filedname varchar (20),
value varchar (20)
)

create table two    
( 
id varchar (20),
code varchar (20)
)

insert into one
values 
('AA','color','red'),
('AA','price','10.2'),
('AA','ID','X1'),
('BB','Color','Blue'),
('BB','price','20.2' ),
('BB','ID','X2')

insert into two 
Values
('X1','123A'),
('X2','123B')