CODEWITHSUNDEEP
sqloracle-database
bbuser
bbuser

Reputation: 928

Combine row pairs with swapped columns

I have a table that contains an even number of rows. The rows form pairs with the same information but the content of the first two columns swapped. Here is an example with three columns:

1  2  3
=======
A  B  W 
B  A  W
C  D  X
D  C  X
E  F  Y
H  G  Z
F  E  Y
G  H  Z

My actual table has many more columns, but the content is always the same within a pair.

I'm looking for an SQL-Statement that gets rid of one row of each pair. The result should look like this:

1  2  3
=======
A  B  W 
C  D  X
E  F  Y
G  H  Z

My table is generated by a script (which I can't change), so I assume my input is correct (every row has a partner, rows >=3 are the same for each pair). A statement that could check these preconditions would be extra cool.

Upvotes: 2

Views: 103

Answers (4)

Jithin Shaji
Jithin Shaji

Reputation: 6073

For Me the below code is working

DECLARE @TEST TABLE
(A CHAR(1),B CHAR(1),C CHAR(1))

INSERT INTO @TEST VALUES
('A','B','W'),
('B','A','W'),
('C','D','X'),
('D','C','X'),
('E','F','Y'),
('H','G','Z'),
('F','E','Y'),
('G','H','Z')

SELECT      MIN(A) [1],
            MAX(A) [2],
            C [3]
FROM        @TEST
GROUP BY    C

Result:

enter image description here

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

If every row has a counterpart where c1 and c2 are swapped then just select rows where c1 and c2 are in a certain order (i.e. c1 < c2).

The EXISTS part makes sure that only rows that have a counterpart are shown. If you want to show all unique rows regardless of whether or not they have a counterpart, then change the last condition from AND EXISTS to OR NOT EXISTS.

SELECT * FROM myTable t1
WHERE c1 < c2
AND EXISTS (
    SELECT * FROM myTable t2
    WHERE t2.c1 = t1.c2
    AND t2.c2 = t1.c1
    AND t2.c3 = t1.c3
) ORDER BY c1

Upvotes: 1

user330315
user330315

Reputation:

You didn't state your DBMS so this is ANSI SQL:

select least(c1,c2), 
       greatest(c1,c2), 
       min(c3) -- choose min or max to your liking
from the_table
group by least(c1,c2), greatest(c1,c2)

Upvotes: 1

ngrashia
ngrashia

Reputation: 9904

Something similar would help using ROWNUM and CTE

with test_Data as
(
SELECT COL1, COL2, COL3, ROWNUM ROWCOUNT FROM
(
SELECT 'A' COL1,  'B' COL2,  'W' COL3 FROM DUAL UNION
SELECT 'B' COL1,  'A' COL2,  'W' COL3 FROM DUAL UNION
SELECT 'C' COL1,  'D' COL2,  'X' COL3 FROM DUAL UNION
SELECT 'D' COL1,  'C' COL2,  'X' COL3 FROM DUAL 
) ORDER BY COL3, COL1
)
SELECT TAB1.COL1, TAB1.COL2, TAB1.COL3 FROM TEST_DATA TAB1, TEST_DATA TAB2
WHERE
TAB1.COL1 = TAB2.COL2
AND TAB1.COL2 = TAB2.COL1
AND TAB1.COL3 = TAB2.COL3
AND TAB1.ROWCOUNT = TAB2.ROWCOUNT+1;

Your query without testdata would be,

with CTE as
(SELECT COL1, COL2, COL3, ROWNUM ROWCOUNT FROM MY_TABLE ORDER BY COL3,COL1)
SELECT TAB1.COL1, TAB1.COL2, TAB1.COL3 FROM CTE TAB1, CTE TAB2
WHERE
TAB1.COL1 = TAB2.COL2
AND TAB1.COL2 = TAB2.COL1
AND TAB1.COL3 = TAB2.COL3
AND TAB1.ROWCOUNT = TAB2.ROWCOUNT+1;

Upvotes: 1

Related Questions