That is excellent guys. Both queries work as expected. I have modified the query a little bit to accomodate a case when col3 is negative. In that case it means that for company col1 is a loss but for comany col2 is a plus.so for examble for the following values;
col1 col2 col3
A B 100
B A -50
A C 200
A D 100
A B 150
B F 200
The result of the modified queries:
SELECT CASE WHEN col1 <= col2 THEN col1 ELSE col2 END AS col1,
CASE WHEN col1 <= col2 THEN col2 ELSE col1 END AS col2,
SUM( CASE WHEN col3 < 0 THEN col3 * -1 ELSE col3 END ) AS col3
FROM SampleData
GROUP BY CASE WHEN col1 <= col2 THEN col1 ELSE col2 END,
CASE WHEN col1 <= col2 THEN col2 ELSE col1 END;
SELECT d.col1, d.col2, col3 = SUM(IIF (d.col3 < 0, d.col3 * -1, d.col3))
FROM (
SELECT col1 = IIF(col1<=col2, col1, col2)
,col2 = IIF(col1<=col2, col2, col1)
,col3
FROM SampleData
) d
GROUP BY d.col1, d.col2
;
Result:
A B 300
A C 200
A D 100
B F 200
That is what I wanted!!
I got all the help I requested. Thank you very much. From my prespective we can close this thread, Not sure who is going to do it.