Simplified view for double-entry inventory transfers

Question

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

Answer 1

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.