Find combination of values on same table in sql server

Question

I have a table like this:

Id  Name
1    x
2    y
3    z

I want a combination like:

Name    Name
x        y
x        z
y        z

this combination should be made from the same table. I tried the query like:

SELECT a.Name, b.Name
FROM table1 a, table1 b
WHERE a.Id != b.Id;

but this produce the result:

Name    Name
y        x
z        x
x        y
z        y
x        z
y        z

which one is not the exact result.

Try using a.Id < b.Id instead.

Felix Pamittan
– Felix Pamittan

2015-04-27 06:29:19 +00:00
Commented Apr 27, 2015 at 6:29 — Felix Pamittan
– Felix Pamittan, Commented Apr 27, 2015 at 6:29

Community · Accepted Answer · 2020-07-29 18:25:24Z

4

Try using < instead of !=:

SELECT 
    a.Name, 
    b.Name
FROM table1 a
INNER JOIN table1 b
    ON a.Id < b.Id

Note: Avoid using the old-style JOIN syntax.

edited Jul 29, 2020 at 18:25

CommunityBot

11 silver badge

answered Apr 27, 2015 at 6:33

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mohan111 · Accepted Answer · 2015-04-27 06:39:50Z

0

declare @t table (id int,name varchar(1))
insert into @t (id,name)values (1,'X'),(2,'Y'),(3,'Z')
;WITH Cte AS(
    SELECT *, 
        RN = ROW_NUMBER() OVER(ORDER BY name)
    FROM @t
)
SELECT 
     t1.name,
     t2.name
FROM Cte c
INNER JOIN Cte cc
    ON cc.RN > c.RN
    AND cc.name != c.name

answered Apr 27, 2015 at 6:39

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Collectives™ on Stack Overflow

Find combination of values on same table in sql server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related