SQL Server – Grouping Rows by Two Columns Without Considering Order

sql server

I have a table looks like this one below:

+----+---------+---------+
| ID | VALUE 1 | VALUE 2 |
+----+---------+---------+
| 01 | A       | B       |
| 01 | B       | A       |
| 02 | C       | D       |
| 02 | D       | C       |
+----+---------+---------+

So what I am trying to do is grouping them together by looking at the value1 and value2 without considering the order of this two values, which mean "A and B" is the same as "B and A".

I am looking for a result looks like this:

+-------+---------+---------+
| COUNT | VALUE 1 | VALUE 2 |
+-------+---------+---------+
| 02    | A       | B       |
| 02    | C       | D       |
+-------+---------+---------+

Does anyone has any idea about how can I get this?

Thanks a lot!

V

Best Answer

Something like:

SELECT COUNT(*) AS C, V1, V2
FROM   (SELECT CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
             , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
        FROM   input_table
       ) AS tbl
GROUP BY V1, V2

should do the trick, but may not be terribly efficient. Any filtering clauses should be added to the inner select, not the outer.

SELECT COUNT(*) AS C
     , CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
     , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
FROM   input_table
GROUP BY CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END, CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END

may also work (and may be more efficient) but the code repetition between the GROUP and SELECT clauses may become a maintenance problem.

Of course if you can ensure that the data is always the "right way around" (and this doesn't break your model in other ways - we can't tell if it might as your questions gives no detail on which to base a supposition either way) then 

SELECT COUNT(*) AS C, Value1, Value2
FROM   input_table
GROUP BY Value1, Value2

is sufficient. You could enforce the two values being the right way around using INSTEAD OF triggers or in your business logic layer (updating existing data should be easy).

Related Question