Removing same records in reverse order after a self join

Question

I'm trying to get these two rows side by side in one row, so I've done an inner join

;WITH data AS 
(
    SELECT 1 id, 'a' AS c1 UNION ALL
    SELECT 1 id, 'b' AS c1
)
SELECT *
FROM data d
INNER JOIN data d2
    ON d.id = d2.id
    AND d.c1 <> d2.c1

This returns two rows, a-b and b-a, which are basically the same but in reverse order. How can I remove this quasi-duplicate?

-- expected results would be just one row (1, a, b)
SELECT 1 d1_id, 'a' AS d1_c1, 'b' AS d2_c1

Note, there could be more than two records sharing the same id. For example,

SELECT 1 id, 'a' AS c1 UNION ALL
SELECT 1 id, 'b' AS c1 union all
SELECT 1 id, 'c' AS c1

sql server 2012

edit: Just figured it out. Row_number will get me what I need.

;WITH data AS 
(
    SELECT *
        ,ROW_NUMBER() OVER(PARTITION BY id ORDER BY c1) rn
    FROM (
    SELECT 1 id, 'a' AS c1 UNION ALL
    SELECT 1 id, 'b' AS c1 union ALL
    SELECT 1 id, 'c' AS c1 
        ) z
)
SELECT *
FROM data d
INNER JOIN data d2
    ON d.id = d2.id
    AND d.c1 <> d2.c1
WHERE d.rn = 1

possible duplicate of [Please explain the parts of a PIVOT](http://stackoverflow.com/questions/5443505/please-explain-the-parts-of-a-pivot) — usr, Jul 30 '14 at 15:10
You're looking for a pivot. Using a join will lead to a contrived solution and not work at all for more than 2 rows. — usr, Jul 30 '14 at 15:11
you have edited your question @Gabe but i have wrote the query same as you but my question (A,b) and (A,c) are coming but what about (B,C)combination — mohan111, Jul 30 '14 at 15:28
@mohan111 Hmm, you're right. (B,C) combination is not being included. I don't how to include that combination of (B,C). Fortunately, the current solution uses 'A' as an anchor to bring in all the other possible values 'B' and 'C'. That might just be enough for my needs. — Gabe, Jul 30 '14 at 15:35

score 2 · Accepted Answer · answered Jul 31 '14 at 06:50

@Gabe, Would this work?

DECLARE @Table TABLE (Col1 VARCHAR(10), Col2 VARCHAR(10))

INSERT  @Table
VALUES  ('B', 'A')
        ,('A', 'B')
        ,('D', 'C')
        ,('C', 'D')
        ,('DD', 'CC')
        ,('CC', 'DD')
        ,('DA', 'CB')
        ,('CA', 'DB')

SELECT  t1.Col1
        ,t1.Col2
FROM @Table AS t1
    LEFT OUTER JOIN @Table AS t2
    ON t1.Col1 = t2.Col2
    AND t1.Col2 = t2.Col1
WHERE   t2.Col1 IS NULL
      OR t1.Col1 < t2.Col1

Thanks Gabe for your responce – Gouri Shankar Aechoor Aug 01 '14 at 17:24 — Gouri Shankar Aechoor, Aug 01 '14 at 17:24

mohan111 · Answer 2 · 2014-07-30T16:28:46.780

;WITH data AS 
(
    SELECT 1 id, 'a' AS c1 UNION ALL
    SELECT 1 id, 'b' AS c1 UNION ALL
    SELECT 1 id, 'C' AS C1 
),Data1 AS
(
SELECT ROW_NUMBER()OVER (PARTITION BY d.ID ORDER BY d2.ID DESC)AS RN,d.id,d.c1,d2.id As ID1,d2.c1 As C2
FROM data d
INNER JOIN data d2
    ON d.id = d2.id
    AND d.c1 <> d2.c1

),REsult AS
    (
    Select CASE WHEN ISNULL(ASCII(d.id),0)< ISNULL(ASCII(d.id1),0) THEN d.c2 ELSE d.c1 END Col1,
     CASE WHEN ISNULL(ASCII(d.id),0)> ISNULL(ASCII(d.id1),0) THEN d.c1 ELSE d.c2 END Col2,
     ROW_NUMBER()OVER (PARTITION BY d.ID ORDER BY d.ID DESC)AS RN FROM Data1 d )

SELECT  
         t1.Col1
        ,t1.Col2
FROM REsult AS t1
    LEFT JOIN REsult AS t2
    ON t1.Col1 = t2.Col2
    AND t1.Col2 = t2.Col1
WHERE   t2.Col1 IS NULL
      OR t1.Col1 < t2.Col1

The row_number has to be in the first CTE before the self-join. The current solution wouldn't return (A,C) combination. See my edit in the question — Gabe, Jul 30 '14 at 15:35

Removing same records in reverse order after a self join

2 Answers2