15,669,524 members
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

## Solution 2

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```

## Solution 1

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.

## Solution 3

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

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

## Solution 4

SELECT A.COUNTRY ||' VS '||B.COUNTRY AS FIXTURES
FROM COUNTRY A, COUNTRY B WHERE A.COUNTRY

CHill60 10-Aug-22 13:52pm
Reason for my downvote: "old-style" join incomplete and invalid where clause.