I have created a request that returns me IDs of rows having the same column value. For example :
x
id | Value
______________
1 | label1
2 | label1
3 | label1
4 | label2
5 | label2
I’d like to get this kind of result :
id | AlternateID | Value
______________________________
1 | 2 | label1
1 | 3 | label1
4 | 5 | label2
The best result I got so far is :
id | AlternateID | Value
______________________________
1 | 2 | label1
2 | 1 | label1
1 | 3 | label1
3 | 1 | label1
4 | 5 | label2
5 | 4 | label2
But as you can see, I have duplicate values across the first two columns
…Right now, without using cursors, I’m stuck.
I am on SQL Server 2008.
Thanks for your help
Advertisement
Answer
Use a derived table to get your base values and join it back to the original table.
SELECT
a.id,
b.id as AlternateID,
a.value
FROM
(SELECT MIN(id) as id , value FROM YourTable GROUP BY value) a
JOIN YourTable b on a.value = b.value and a.id <> b.id