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?

  • Sorry it removed the formatting of my table. How can I post the table structure here so my question will be clear to the reader? – Harvey A. Oct 18 '16 at 19:17
  • I have updated it to show the columns, in the future, I always use code blocks for posting information like this. – James Rhoat Oct 18 '16 at 19:30

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
    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
('BB','price','20.2' ),

insert into two 
  • 1
    PIVOT is for SQL Server. The OP hasn't tagged the question with a dbms. (plus, you are missing a lot of terminators ;) – ypercubeᵀᴹ Oct 18 '16 at 20:08
  • I just assumed :P I don't mind spending some time on going through it either way :) What do you mean I am missing terminators? – James Rhoat Oct 18 '16 at 20:09
  • 1
    I mean the funny little character that should be at the end of every statement ; (and yeah, I know Microsoft has allowed us to be lazy. So far.) – ypercubeᵀᴹ Oct 18 '16 at 20:15
  • Haha, ahhh semi-colon. That looked like a winking face at the end. My brain automatically converted it lol – James Rhoat Oct 18 '16 at 20:21
  • 1
    It was intentionally both ;) – ypercubeᵀᴹ Oct 18 '16 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.