Data from Multiple Rows

Question

User-333609119 posted

Good day all!

I am trying to move data from one table to another using a query. I need to move multiple rows of data from one table to one row with multiple columns in another table.

Table 1 has 3 rows of data that belong to one part Id.

partId, clearanceName, clearanceValue, dateAdded
1234, Clearance Upper, 1234, 2018-04-11
1234, Clearance Lower, 5678, 2018-04-11
1234, Clearance Total, 0246, 2018-04-11

Table 2 has 3 columns that need values from table 1:
partId, clearanceUpperValue, clearanceLowerValue, clearanceTotalValue, dateAdded

I would like to take the values from table 1 and put them in their respective columns in table 2.

Thank you!

Answer 1

User-333609119 posted

I was able to successfully run this query:

INSERT INTO tbl2 (partID, clearanceUpperValue, clearanceLowerValue, clearanceTotalValue, dateAdded)
SELECT
   partID,
   MAX(clearanceValue) AS clearanceUpperValue,
   MAX(clearanceValue) AS clearanceLowerValue,
   MAX(clearanceValue) AS clearanceTotalValue,
   dateAdded
FROM
   tbl1
GROUP BY
   partID, dateAdded

But it is now making duplicate rows when I only want 1 row.

Answer 2

User-333609119 posted

Need to add DISTINCT to my SELECT. Thank you all!

Never mind. That's wrong. Putting same data in all 3 columns when they are supposed to be different.

Answer 3

User1535942433 posted

Hi nbrglobalinc,

Putting same data in all 3 columns when they are supposed to be different.

I don't understand your requirement clearly.I'm guessing that the result like this:

partId, clearanceName, clearanceValue, dateAdded
1234, Clearance Upper, 1234, 2018-04-11
1234, Clearance Lower, 5678, 2018-04-11
1234, Clearance Total, 0246, 2018-04-11

1234, Clearance Total, 0234, 2018-04-11

result:

partId, clearanceUpperValue, clearanceLowerValue, clearanceTotalValue, dateAdded

1234    1234                               5648                            0246,0234                2018-04-11

If the guessing is right,you could use pivot. What's column you group by ?

Please  tell us more details of your requirement.It will help us to solve your problems.

Best regards,

Yijing Sun