3

I'm trying to get a clean view of a table showing product transfers between warehouses. There are 2 records for each transfer: a "FROM" record debiting the sending warehouse and a "TO" record crediting the receiving warehouse. The (simplified) data looks like this:

Transfer

TransNo ItemNumber FromToDesc Location TransEachQty
236393 176 FROM 1 -10
236393 176 TO 3 10
236393 386 FROM 1 -60
236393 386 TO 3 60
236393 659 FROM 1 -14
236393 659 TO 3 14

I want to end up with a simplified one-record-per-transfer view like this:

TransNo ItemNumber FromLocation ToLocation TransQty
236393 176 1 3 10
236393 386 1 3 60
236393 659 1 3 14

I'm not sure how to get there from the table I'm starting with.

Recommendations, please?

SQL Fiddle

1 Answer 1

4

Seems like it's pretty simple conditional aggregation

SELECT
  TransNo,
  ItemNumber,
  FromLocation = MIN(CASE WHEN FromToDesc = 'FROM' THEN Location END),
  ToLocation   = MIN(CASE WHEN FromToDesc = 'TO'   THEN Location END),
  TransQty = MAX(TransEachQty)
FROM Transfer
GROUP BY
  TransNo,
  ItemNumber;

SQL Fiddle

If there are more than one FROM and TO per grouping, or the positives and negatives are reversed, then the results may not be correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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