-1

I need sql query which sums all of the possible raw pairs in the table.

My table looks like this :

ID  | Name | Value
1   |  A   | 100
2   |  B   | 150
3   |  C   | 250
4   |  D   | 600

In this case the query output should be :

FistName  | SecondName | Sum
     A    |  B         | 250
     A    |  C         | 350
     A    |  D         | 700
     B    |  C         | 400
     B    |  D         | 750
     C    |  D         | 850
Salman A
  • 262,204
  • 82
  • 430
  • 521
Vova Klimov
  • 42
  • 5
  • 1
    its a simple cross join of a table with itself, and a select on that. what is your problem, where is your sql statement, what did you try to solve this, where is your solution wrong? – Patrick Artner Nov 08 '18 at 11:17
  • 1
    Do you want all combinations or permutations? In the latter case {A, B} is different to {B, A}; you're showing combinations... – Richard Nov 08 '18 at 11:20
  • 1
    Possible duplicate of [combinations (not permutations) from cross join in sql](https://stackoverflow.com/questions/7112513/combinations-not-permutations-from-cross-join-in-sql) – yivi Nov 08 '18 at 12:39

4 Answers4

4

Try this:

select 
    t1.Name as FirstName,
    t2.Name as SecondName,
    t1.Value+t2.Value as Sum
from yourtable as t1
inner join yourtable as t2 on (t1.ID<t2.ID)
George Menoutis
  • 6,894
  • 3
  • 19
  • 43
2

Just INNER JOIN based on the condition that l.ID < r.ID. This ensures that the row is not joined to itself and there are no duplicate in the form of 1, 2 and 2, 1:

DECLARE @t TABLE (ID INT, Name VARCHAR(100), Value INT);
INSERT INTO @t VALUES
(1, 'A', 100),
(2, 'B', 150),
(3, 'C', 250),
(4, 'D', 600);

SELECT l.Name FirstName, r.Name SecondName, l.Value + r.value [Sum]
FROM @t AS l
INNER JOIN @t AS r ON l.ID < r.ID
ORDER BY FirstName, SecondName
Salman A
  • 262,204
  • 82
  • 430
  • 521
0

This is one approach that works:

CREATE TABLE #T (ID INT, Name VARCHAR (10), VALUE INT)
INSERT INTO #T VALUES (1, 'A', 100), (2, 'B', '150'), (3, 'C', 250), (4, 'D', 600)

SELECT CASE WHEN T.Name < T2.Name THEN (T.Name + T2.Name)  
          ELSE (T2.Name + T.Name) 
      END AS FullName,
      SUM (T.Value) AS TotalValue
FROM #T AS T
FULL OUTER JOIN #T AS T2 ON T.Name <> T2.Name
GROUP BY CASE WHEN T.Name < T2.Name THEN (T.Name + T2.Name)  
            ELSE (T2.Name + T.Name)  END
SQL_M
  • 2,455
  • 2
  • 16
  • 30
  • 2
    Seems significantly over complicated compared to a half-cartesian-product as per other answer here? – MatBailie Nov 08 '18 at 11:33
  • I'm sure there are even more complex ways to get the expected result :-) – dnoeth Nov 08 '18 at 11:34
  • @MatBailie This is 1 case statement, 1 join and 1 groupby. Significantly over complex might be significantly exaggerated. :) – SQL_M Nov 08 '18 at 12:17
  • Compared to one join it is significantly more complicated (and might return a wrong answer if `name` is not unique) – dnoeth Nov 08 '18 at 13:59
-1

A chance to use the rarely used Cartesian Join, with the cross join operator:

select left.Name, right.Name, left.Value + right.Value as Sum
from DataTable left
  cross join DataTable right
order by left.Name, right.Name;

Note, is the input table is large this will lead to a lot of rows. To avoid rows being combined both ways and to themselves ({A,B} and {B,A} appearing, {A,A} appearing) conditions can be added.

Richard
  • 106,783
  • 21
  • 203
  • 265