CODEWITHSUNDEEP
sqlsql-servert-sql
Sriram Vanamamalai
Sriram Vanamamalai

Reputation: 41

Grouping of pairs in sql

I would like to group (a,b) and (b,a) into one group (a,b) in SQL

For e.g the following set 

SELECT 'a' AS Col1, 'b' AS Col2
UNION ALL
SELECT 'b', 'a'
UNION ALL
SELECT 'c', 'd'
UNION ALL
SELECT 'a', 'c'
UNION ALL
SELECT 'a', 'd'
UNION ALL
SELECT 'b', 'c'
UNION ALL
SELECT 'd', 'a'

should yield

Col1 | Col2
a       b
c       d
a       c
a       d
b       c

Upvotes: 3

Views: 116

Answers (4)

paparazzo
paparazzo

Reputation: 45096

select col1, col2 from table 
except 
select col2, col2 from table where col2 > col1

Upvotes: 0

qxg
qxg

Reputation: 7036

By value, (a, b) doesn't equal to (b, a). If you want to treat them the same, you're creating new key column(s) for each row. The overall logic would be

WITH TableWithNewKey
(
    SELECT <original columns>, <new columns as key>
    FROM originaltable
)
SELECT <new columns as key>, Aggregate(...)
FROM TableWithNewKey
GROUP BY <new columns as key>

In your case, <new columns as key> is to convert (b, a) to (a, b). It can be case statement to swap a and b or other functions. This form applies to any number of columns and other equality comparison.

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32392

Group by a case statement that selects the pairs in alphabetical order:

select case when col1 < col2 then col1 else col2 end as col1,
case when col1 < col2 then col2 else col1 end as col2
from (
    select 'a' as col1, 'b' as col2
    union all
    select 'b', 'a'
    union all
    select 'c', 'd'
    union all
    select 'a', 'c'
    union all
    select 'a', 'd'
    union all
    select 'b', 'c'
    union all
    select 'd', 'a'
) t group by case when col1 < col2 then col1 else col2 end,
case when col1 < col2 then col2 else col1 end

http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/6977

If you simply want unique values (as opposed to a grouping for aggregation) then you can use distinct instead of group by

select distinct case when col1 < col2 then col1 else col2 end as col1,
case when col1 < col2 then col2 else col1 end as col2
from (
    select 'a' as col1, 'b' as col2
    union all
    select 'b', 'a'
    union all
    select 'c', 'd'
    union all
    select 'a', 'c'
    union all
    select 'a', 'd'
    union all
    select 'b', 'c'
    union all
    select 'd', 'a'
) t

Upvotes: 4

trincot
trincot

Reputation: 350310

As an alternative, you could use a UNION to achieve this:

WITH cte AS (
    SELECT 'a' AS Col1, 'b' AS Col2
    UNION ALL
    SELECT 'b', 'a'
    UNION ALL
    SELECT 'c', 'd'
    UNION ALL
    SELECT 'a', 'c'
    UNION ALL
    SELECT 'a', 'd'
    UNION ALL
    SELECT 'b', 'c'
    UNION ALL
    SELECT 'd', 'a')
SELECT col1, col2 FROM cte WHERE col1 < col2 OR col1 IS NULL
UNION
SELECT col2, col1 FROM cte WHERE col1 >= col2 OR col2 IS NULL
ORDER BY 1, 2

SQL fiddle

Note that a UNION removes duplicates.

If you don't have NULL values, you can of course omit the OR part in the WHERE clauses.

Upvotes: 2

Related Questions