Merge two lines into one after joining

Question

I get data from the following form from an external program, so I have no way to change the way elements are presented. The idea is that one element consists of (xy) and for some (unclear) reason, translated into two different lines when the program displays these measures. I would like to return it in one line with the sum of the measure as a 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 that duplicates the lines:

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

Please feel free to ask for any clarification.

EDIT: It seemed that there was a “sequence” in the “names” and “right”. Not there.

Answer 1

Usage GROUP BY:

, (x y), , (FirstCol LastCol) 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

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

---

: , xy ( ). , Measure:

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

:

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];