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.
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.
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
57 Points
191 Posts
Data from Multiple Rows
Feb 11, 2021 05:58 PM|nbrglobalinc|LINK
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!
Member
57 Points
191 Posts
Re: Data from Multiple Rows
Feb 11, 2021 07:03 PM|nbrglobalinc|LINK
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.
Member
57 Points
191 Posts
Re: Data from Multiple Rows
Feb 11, 2021 07:11 PM|nbrglobalinc|LINK
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.
Contributor
3730 Points
1419 Posts
Re: Data from Multiple Rows
Feb 12, 2021 05:19 AM|yij sun|LINK
Hi nbrglobalinc,
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