Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a table with these columns like,
country
-------
India
Pak
Aus
I need to generate the output like,
Output:
-------
India vs Aus
India vs Pak
Pak vs Aus


What I have tried:

I have tried with Sqlsever joins with using row_numbers() but i unable to solve . Can anyone help me out this.
Posted
Updated 1-Apr-19 5:44am
v2

You are on the right track with using a JOIN; the tricky part is choosing which operator(s) to use on it.

SQL
DECLARE @table TABLE (
   ndx INT IDENTITY(1,1) NOT NULL,
   Team VARCHAR(16)
)
INSERT @table
VALUES ('One')
,      ('Two')
,      ('Three')

SELECT  t1.Team, t2.Team
FROM       @table  t1
INNER JOIN @table  t2 ON t1.ndx < t2.ndx

Will return you this
Home  Guest
----  -----
One   Two
One   Three
Two   Three
 
Share this answer
 
Google is your friend: be sure to visit him often.

A basic google gave me over 6 million hits: combinations sql - Google Search[^] - follow any link (pretty much) and you'll find the solution.
 
Share this answer
 
WITH teams
AS (SELECT 'India' team
FROM dual
UNION ALL
SELECT 'Pak' team
FROM dual
UNION ALL
SELECT 'Aus' team
FROM dual
)
SELECT DISTINCT CASE
WHEN t1.team >= t2.team THEN t2.team
ELSE t1.team
END || ' VS '||
CASE
WHEN t1.team >= t2.team THEN t1.team
ELSE t2.team
END Matches
FROM teams t1
cross join teams t2
WHERE t1.team != t2.team
ORDER BY Matches;

OUTPUT :
-------
Matchs
Aus VS India
Aus VS Pak
India VS Pak
 
Share this answer
 
Comments
CHill60 18-Sep-20 4:48am    
Reasons for my downvote:
- the question is clearly tagged as SQL-Server but this solution is written for Oracle (or another database that uses dual - definitely not SQL-Server)
- It's a really inefficient query. A simple self-join is enough - see Solution 2
SELECT A.COUNTRY ||' VS '||B.COUNTRY AS FIXTURES
FROM COUNTRY A, COUNTRY B WHERE A.COUNTRY
 
Share this answer
 
Comments
CHill60 10-Aug-22 13:52pm    
Reason for my downvote: "old-style" join incomplete and invalid where clause.
Also - old question already answered

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900