Merge two rows into one after a join

Question

I'm given data on the following form from an external program, thus I have no way to modify the way it represents the elements. The idea is that a single element consists of (xy) and is for some (obscure) reason translated into two different rows when the program outputs the measures. I would like to turn it back into a single row with the sum of the measure as the result. Below is the data-format.

+------------+---------+---------+---------+
|    Manager |    x    |    y    | Measure |
+------------+---------+---------+---------+
| A          | Left    | Right   | 10      |
| A          | Right   | Left    | -9      |
| A          | Venstre | Hojre   | 4       |
| A          | Hojre   | Venstre | -1      |

                     ...
+------------+---------+---------+---------+

This is what I would like to have:

+------------+---------+---------+---------+
|    Manager |    x    |    y    | Measure |
+------------+---------+---------+---------+
| A          | Left    | Right   | 1       |
| A          | Venstre | Hojre   | 3       |
                    ...
+------------+---------+---------+---------+

My current simple join, which duplicates the rows:

SELECT * FROM _table s1
JOIN _table s2
ON s1.manager = s2.manager 
AND s1.x = s2.y
AND s2.y = s1.x

Please don't hesitate to ask for any clarification.

EDIT: Seemed like there was a 'consistency' in the naming of 'Left' and 'Right'. There's not.

Answer 1

Using GROUP BY:

The idea is to rearrange the columns (x and y), in this case alphabetically, and then use the arranged columns (FirstCol and LastCol) in your GROUP BY

SQL Fiddle

WITH Cte AS(
    SELECT *,
        FirstCol = CASE WHEN x <= y THEN x ELSE y END,
        LastCol = CASE WHEN x <= y THEN y ELSE x END
    FROM tbl
)
SELECT
    Manager,
    x = FirstCol,
    y = LastCol,
    Measure = SUM(Measure)
FROM Cte
GROUP BY Manager, FirstCol, LastCol

Result

| Manager |     x |      y | Measure |
|---------|-------|--------|---------|
|       A |  Left |  Right |       1 |
|       A | Left2 | Right2 |       3 |

---

Edit: This solution will retain the original column order if there is only one xy combination (no pair). Additionally, it will retain the order of the row with the higher Measure value:

SQL Fiddle

WITH Cte AS(
    SELECT
        Manager,
        x,
        y,
        RN =    ROW_NUMBER() OVER(
                    PARTITION BY 
                        Manager,
                        CASE WHEN x <= y THEN x ELSE y END,
                        CASE WHEN x <= y THEN y ELSE x END
                    ORDER BY
                        Measure DESC
                ),

        Measure = SUM(Measure) OVER(
                    PARTITION BY 
                        Manager,
                        CASE WHEN x <= y THEN x ELSE y END,
                        CASE WHEN x <= y THEN y ELSE x END
                  )
    FROM tbl
)
SELECT
    Manager, x, y, Measure
FROM Cte
WHERE Rn = 1

Answer 2

You can try this:

DECLARE @DataSource TABLE
(
    [Manager] CHAR(1)
   ,[x] CHAR(8)
   ,[y] CHAR(8)
   ,[Measure] SMALLINT
);

INSERT INTO @DataSource ([Manager], [x], [y], [Measure])
VALUES ('A', 'Left', 'Right', 10)
      ,('A', 'Right', 'Left', -9)
      ,('A', 'Left2', 'Right2', 4)
      ,('A', 'Right2', 'Left2', -1);

SELECT DS1.[Manager]
      ,DS1.[x] AS [x]
      ,DS2.[x] AS [y]
      ,DS1.[Measure] + DS2.[Measure] AS [Measure]
FROM @DataSource DS1
INNER JOIN @DataSource DS2
    ON DS1.[x] = DS2.[y]
    AND DS1.[y] = DS2.[x]
    AND DS1.[Manager] = DS2.[Manager]
WHERE DS1.[Measure] > DS2.[Measure];