May 23, 2007 at 6:30 am
Good day,
I have a staging table, mc_staging, that has 6 columns (item, branch, month, source, inv_value, cme_value)
Each item has 2 values for each branch, 1 inv_value and 1 cme_value- therefore there are 2 rows for each distinct record.
I want to insert this data into another table, but i want these items to have both the inv_value and cme_value on the same row. i want to end up with 1 row for each item for each branch, with both the inv and cme values.
How can i accomplish this??
May 23, 2007 at 7:23 am
I don't know if I understand you correctly but I think you could get the records combined by using something like this
INSERT INTO realtable
(
...your fields....
)
SELECT
item,
branch,
month,
source
SUM(inv_value),
SUM(cme_value)
FROM mc_staging
WHERE ...your condition...
GROUP BY item, branch, month, source
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 23, 2007 at 7:56 am
Assuming that the inv_value and cme_value might not be numerical, you could also do it this way. This is also assuming that the item column is distinct for each item (assuming there will be two records for each item, one for each value).
INSERT INTO realtable
(
...your fields....
)
SELECT
t1.item,
t1.branch,
t1.month,
t1.source
t1.inv_value,
t2.cme_value
FROM
mc_staging t1 INNER JOIN
ms_staging t2 ON t1.item = t2.item
May 23, 2007 at 10:37 am
HI all,
thanks for the responses. I came to the same conclusion and I actually managed to achieve my goal with the following script:
INSERT INTO MC_TEST (itemnmbr, old_branch,[month], inv_VALUE, cme_value)
SELECT a.itemnmbr, a.old_branch, a.[month], a.inv_VALUE, b.cme_value
FROM MC_STAGING a inner join mc_staging b on
a.itemnmbr = b.itemnmbr and
a.old_branch = b.old_branch
WHERE a.SOURCE = 'Invoice Qty' and
b.source = 'Contribution Margin Equalized'
Thanks for the posts anyway, at least it confirms that the way i was doing it was the right way.
Later.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply