CODEWITHSUNDEEP
sql-serveruniquecombinationsmultiple-columns
Horia
Horia

Reputation: 39

SQL Server - count on unique combination from two columns

I have the following table in SQL Server:

Name1       Name2   Amount

Larry       George  1
Larry       Michael 1
Michael     George  1
Michael     Larry   1
Larry       George  1
George      Larry   1

It would be great if someone could provide me a script for getting the following result (which is the first name does not matter e.g. Larry / George = George / Larry):

Name_combination  Amount
Larry / George    3
Larry / Michael   2
Michael / George  1

Many thanks in advance

Upvotes: 0

Views: 525

Answers (1)

fthiella
fthiella

Reputation: 49049

You could concatetate Name1 with Name2 when Name1 is < than Name2, or Name2 with Name1 otherwise:

SELECT
  CASE WHEN Name1<Name2 THEN Name1 + ' / ' + Name2
                        ELSE Name2 + ' / ' + Name1 END AS Name_combination,
  SUM(Amount) AS Amount
FROM
  tablename
GROUP BY
  CASE WHEN Name1<Name2 THEN Name1 + ' / ' + Name2
                        ELSE Name2 + ' / ' + Name1 END

Please see an example fiddle here.

Upvotes: 1

Related Questions