CODEWITHSUNDEEP
sqlsql-server
Rajeev Kumar
Rajeev Kumar

Reputation: 4963

Fetch single row if two columns in table have same values either ways

I have a table with columns caller and callee with following values say

caller callee
999 888
888 999
999 555
555 333
555 999

now i want only single row in returned as

caller1    caller2  count 
999        888      2
999        555      1
555        333      1
555        999      2

Upvotes: 6

Views: 610

Answers (1)

Martin Smith
Martin Smith

Reputation: 452978

SELECT CASE
         WHEN caller < callee THEN callee
         ELSE caller
       END      AS caller1,
       CASE
         WHEN caller < callee THEN caller
         ELSE callee
       END      AS caller2,
       Count(*) AS [Count]
FROM   YourTable
GROUP  BY CASE
            WHEN caller < callee THEN callee
            ELSE caller
          END,
          CASE
            WHEN caller < callee THEN caller
            ELSE callee
          END

Upvotes: 6

Related Questions