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

```