June 15, 2016 at 6:34 am
Hi All,
After a long time i am back here, great to see you all.
I have an requirement where below is the table having the records.
a
b
c
d
e
no duplicate should be there in the data, say this is a football team and they have to meet each team only once.
Output
C1 C2
ab
ac
ad
ae
bc
bd
be
cd
ce
de
June 15, 2016 at 6:47 am
Hi,
What about this
with cte(team,ord) as
(
select 'a',1 union
select 'b',2 union
select 'c',3 union
select 'd',4 union
select 'e',5
)
select t1.team,t2.team
from cte as t1
inner join cte as t2 on t1.team != t2.team and t2.ord > t1.ord
order by t1.team
Igor Micev,My blog: www.igormicev.com
June 16, 2016 at 7:53 am
This problem can be solved using CROSS JOINs as well.
My solution is followed:
create table #tblfootball
(
ID INT IDENTITY(1,1)
,NAME VARCHAR(100)
);
INSERT #tblfootball(NAME)
VALUES('a'),('b'),('c'),('d'),('e');
select *
from #tblfootball;
select row_number() over(order by t1.name) as matchnum, t1.name as c1, t2.name as c2
from #tblfootball t1
cross join #tblfootball t2
where t1.id < t2.id
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply